Cascade update trigger FOR EACH ROW

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cascade update trigger FOR EACH ROW

Author

Topic

mznSQL
Starting Member

2 Posts

Posted - 2007-10-28 : 04:13:39

hi every one
i have this 2 tables and i want to make cascade UPDATE and cascade DELETE
i tried using relations but it failed
then i used triggers
this is the script i an using:
============================================================
CREATE TABLE Table1 (
TransSN [int] NOT NULL ,
TransName [nvarchar] (50) NOT NULL ,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(TransSN) ON [PRIMARY] ) ON [PRIMARY]

CREATE TABLE Table2 (
MasterSN [int] NOT NULL ,
SlaveSN [int] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(MasterSN,SlaveSN) ON [PRIMARY] ) ON [PRIMARY]

go

CREATE TRIGGER trgUpdateMasterSnInTable2 ON Table1
FOR UPDATE
AS

set nocount on

declare @NewSN int , @OldSN int

select @NewSN = inserted.TransSN from inserted
select @OldSN = deleted.TransSN from deleted

update Table2 set MasterSN = @NewSN
where MasterSN = @OldSN

update Table2 set SlaveSN = @NewSN
where SlaveSN = @OldSN

set nocount off
go
==================================================================
NOW
this script is working as cascade UPDATE but only for one row updating

my question is :
how can i modify this script to make it work as cascade update FOR EACH ROW ???

please help

Kristen
Test

22859 Posts

Posted - 2007-10-28 : 06:22:53

Triggers do not fire for each record, they fire for each statement, so you must write your trigger to handle multiple rows. Probably something like this:


update U
set MasterSN = I.TransSN
FROM deleted AS D
     JOIN inserted AS I
         ON I.MyPK = D.MyPK
     JOIN Table2 AS U
         ON U.MasterSN = D.TransSN

update U
set SlaveSN = I.TransSN
FROM deleted AS D
     JOIN inserted AS I
         ON I.MyPK = D.MyPK
     JOIN Table2 AS U
         ON U.SlaveSN = D.TransSN

Kristen

mznSQL
Starting Member

2 Posts

Posted - 2007-10-28 : 07:14:32

Dear Kristen
thanx alot for your reply and it is realy appreciated
but the problem is:
when you made join between inserted table and deleted table you used (MyPK field) which is invalid
i know its a primary key field but in my sample the primary key in Table1 is (TransSN) and this field should be related to both (MasterSN & SlaveSN) in Table2 and this relation should be CASCADE UPDATE & CASCADE DELETE

i am tring to attach an image about what i want because i do it in ACCESS but i dont know how to attach image

thank U again & i hope i get what i am looking for

mazen...

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:37:16

You will HAVE to have a unique field (or multi-field combination which is unique) that does NOT change between Old/New data. An IDENTITY column or UNIQUEIDENTITY (GUID) would do for that purpose. (Otherwise there is NO way to associate the Old Row with the New Row in the trigger)

Very VERY bad form to change the PK on a record that has dependencies ... so if that is what you are trying to do you'll be finding out that one the hard way!!

If you have a Foreign Key Constraint on this relationship you will NOT be able to change the Key Field on the Parent

You will have to:

Insert duplicate parent record with new Key value (which may cause duplicates in other unique constraints

)
Update Child record to the new Key value
Delete old Parent record

all of which is a PITA and why people go down the route of using surrogate keys ...

Did I say it was a Bummer! already?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources