Cascading Update with Trigger - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Cascading Update with Trigger

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-10 : 16:40:50

Hi,
The multiple cascading paths is an error that happened sometime. For solving this problem there is only one way, TRIGGER.
Assume we have two tables T1 is the Parent and T2 is the Child that reference to T1.
I want implementing cascading update with using Trigger.
My approach is following but I do not know what the correct technique is. Could somebody show the correct Trigger code?

create T1 (i int primary key not null)
create T2 (id int primary key not null, 
           i int --references T1(i)
          );

create trigger trg1 on T1
after update as 
begin
	update c
	   set i = i.i
   	  from T2 c
  	    join (select *, rn=row_number() over(order by (select null)) 
	            from deleted)d
 	      on c.i = d.i
	    join (select *, rn=row_number() over(order by (select null)) 
	            from inserted)i
	      on d.rn = i.rn;
end;

Note: I need a solution that be suitable with multiple rows, not only one row.
______________________

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 10:35:57

why do you need join with deleted?
also what all fields do T2 have? usually it refers only ID field of T1 through FK relationship. In that case ID never changes so didnt understand what you want update on child table? Or am i missing something?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-11 : 10:39:58

Do this

Post ome DDL for the tables

Post some sample DML to insert the Data

Show us what DML operation you expect to happen

Then Show us what the final result is suppose to be

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-11 : 11:21:30

Assume we want update the related Child tables columns after updating PK of Parent table.
These tables:

create table Parent
(user_ID integer not null primary key, 
 user_name char(50) not null);
go

create table Child 
(author_ID integer not null primary key, 
 author_name char(50) not null, 
 lastModifiedBy integer not null, 
 addedby integer not null)
go

And some rows on it:

INSERT INTO Parent
VALUES (1, 'user_1'),
       (2, 'user_2');
       
INSERT INTO Child
VALUES (1, 'author_1', 1, 2),
       (2, 'author_2', 2, 1);

I need to update two lastModifiedBy and addedby columns after updating user_id in Parent table.
So after this:

UPDATE P 
SET user_id = D.user_id
FROM Parent P
JOIN (SELECT 10, 1
       UNION ALL
      SELECT 20, 2) AS D(user_id, old)
ON P.user_id = D.old

The Child table data must be somethink like this:

author_id   author_name lastModifiedBy addedby
----------- ----------- -------------- -----------
1           author_1    10             20
2           author_2    20             10

I have used following trigger but I do not believe this trigger is correct because nothing guarantees the order of rows in the Inserted and Deleted tables and I cannot just match based on some arbitrarily row number.

CREATE TRIGGER trg ON Parent
AFTER UPDATE AS 
BEGIN
	--Update lastModifiedBy Column
	--Update addedby Column
	;WITH i AS
	(SELECT *, rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
	   FROM inserted),
	      d AS
	(SELECT *, rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
	   FROM deleted),
	      insert_delete AS
	 (SELECT d.user_id AS old_user_id, 
	         i.user_id AS new_user_id
	    FROM i
	      JOIN d
	        ON i.rn = d.rn)
              
	UPDATE C
	   SET lastModifiedBy = D1.new_user_id,
	       addedby = D2.new_user_id
   	  FROM Child C
  	    JOIN insert_delete D1
 	      ON C.lastModifiedBy = D1.old_user_id 	      
	    JOIN insert_delete D2
	      ON C.addedby = D2.old_user_id; 
END

Thanks a lot.

______________________

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-08-11 : 11:45:03

I find the correct solution. I can use OUTPUT to solve it.

______________________

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-11 : 13:05:43

Glad we could help....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources