Hi to all...
I've a little trouble with a t-sql db...
I've 2 tables int he db.
On the child table i've a trigger that do some controls and some
calcs
that are saved on a 3 table.
When i've created the db, i've added the reference from the child to
the parent, with the option ON DELETE CASCADE.
The problem is that when i remove the child (via the reference) rows,
i must access some data that are on the parent table.
What i can do to access with the trigger to the parent row data while
removing the child rows?

Follow the SQL code that give me the problem:
----------------------------------------------------


CREATE DATABASE Attrezzature


use attrezzature


CREATE TABLE Movimenti (id int NOT NULL IDENTITY (1, 1) PRIMARY KEY
("id"), cod_movimento int)


CREATE TABLE AttrezzatureMovimento (id int NOT NULL IDENTITY (1, 1)
PRIMARY KEY ("id"), id_movimento int REFERENCES movimenti(id) ON
DELETE CASCADE)


INSERT INTO movimenti (cod_movimento) VALUES (333)


INSERT INTO AttrezzatureMovimento (id_movimento) VALUES (1)


CREATE TRIGGER TR_DEL_QTAMovimento
ON AttrezzatureMovimento
FOR DELETE
AS


DECLARE @test int
SET @test = (select cod_movimento from movimenti where id = (select
id_movimento from Deleted))


if (@test IS NULL)
BEGIN
RAISERROR ('Impossibile predere i dati dalla tabella padre!',
18,1)
ROLLBACK TRANSACTION
RETURN
END


delete from movimenti where id = 1


----------------------------------------------------


What i can do???


Very thanks!

Masterx81 (enrico@je.net) writes:
> Hi to all...
> I've a little trouble with a t-sql db...
> I've 2 tables int he db.
> On the child table i've a trigger that do some controls and some
> calcs
> that are saved on a 3 table.
> When i've created the db, i've added the reference from the child to
> the parent, with the option ON DELETE CASCADE.
> The problem is that when i remove the child (via the reference) rows,
> i must access some data that are on the parent table.
> What i can do to access with the trigger to the parent row data while
> removing the child rows?

I think you should change the FK to NO ACTION, that is so that it
disallows deleting rows from the parent table that are referenced
by the subtable. You should do this, since you have a requirement
when a subtable row is deleted, the parent must still be there.

This would of course require the application to delete things in
the right order. But you have both have the cake and eat it.

> DECLARE @test int
> SET @test = (select cod_movimento from movimenti where id = (select
> id_movimento from Deleted))

You may have this code only for the sake of the example, but since it is
a common error I like to point out that since a trigger fires once
per statement, there can be multiple rows in the inserted/deleted
tables, why you cannot assign data into variables, or run subqueries
like the one above.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Don't multi-post, people miss valid discussion.

http://groups.google.ca/group/micros...a27a8fe98b2d7a

On Jul 6, 6:08 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Masterx81 (enr...@je.net) writes:
> > Hi to all...
> > I've a little trouble with a t-sql db...
> > I've 2 tables int he db.
> > On the child table i've a trigger that do some controls and some
> > calcs
> > that are saved on a 3 table.
> > When i've created the db, i've added the reference from the child to
> > the parent, with the option ON DELETE CASCADE.
> > The problem is that when i remove the child (via the reference) rows,
> > i must access some data that are on the parent table.
> > What i can do to access with the trigger to the parent row data while
> > removing the child rows?
>
> I think you should change the FK to NO ACTION, that is so that it
> disallows deleting rows from the parent table that are referenced
> by the subtable. You should do this, since you have a requirement
> when a subtable row is deleted, the parent must still be there.
>
> This would of course require the application to delete things in
> the right order. But you have both have the cake and eat it.
>
> > DECLARE @test int
> > SET @test = (select cod_movimento from movimenti where id = (select
> > id_movimento from Deleted))
>
> You may have this code only for the sake of the example, but since it is
> a common error I like to point out that since a trigger fires once
> per statement, there can be multiple rows in the inserted/deleted
> tables, why you cannot assign data into variables, or run subqueries
> like the one above.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx