|
|
Posted by Erland Sommarskog on 07/06/07 22:08
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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|