|
|
Posted by Jason Lepack on 07/09/07 12:31
Don't multi-post, people miss valid discussion.
http://groups.google.ca/group/microsoft.public.sqlserver.programming/browse_thread/thread/5f8127beec3121c8/98a27a8fe98b2d7a#98a27a8fe98b2d7a
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
Navigation:
[Reply to this message]
|