Trigger to Update - 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)

Trigger to Update

Author

Topic

nandithab
Starting Member

6 Posts

Posted - 2008-02-12 : 06:57:51

My Table Structure is:

Table A Table B Table C
colA -PK Col B-PK Col C-PK
Col B-FK Col B-FK
Col C-FK

This relation establish a Concurrent relation where in Cascade Property fails.I can set Cascade property for any two tabnles...but not the third table.
My requirement is :
1)To Update PK of Table B
On updation of Table B,the dependent tables of Table B(ie,Table A and Table C) should also update with the updated ID.
I can set Cascade Property for Update between Table B and Table C and between Table B and Table A.

2)When i update PK of table C,i want its dependent table(ie,table A) also to update with new updated ID.
Here I cannot set Cascade property between Table A and Table C For Updation of Col C.Its because referential Integrity fails in thsi scenario due to
Concurrent realtion between 3 tables.
So what i can do is,i have to create a trigger which updates Table A on updating Table C.
So i have created a trigger As given below:

CREATE TRIGGER "[ Tbl C UTrig]" ON [Tbl C] FOR UPDATE AS

SET NOCOUNT ON

/* * CASCADE UPDATES TO '[Tbl A]' */

IF UPDATE([Col C])

BEGIN

UPDATE [Tbl A]

SET [Tbl A]. [Col C]=inserted. [Col C]

FROM [Tbl A], deleted, inserted

WHERE deleted. [Col C]= [Tbl A]. [Col C]

END

Problem with this trigger is:
It will update table A with only last value of Table C.ie it updates all rows of col C of table A with the last value of Table C.
Can anyone suggest me teh solution for this?
Do i need to loop through every row ?if yes,How?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 07:06:58

[code]CREATE TRIGGER "[ Tbl C UTrig]" ON [Tbl C] FOR UPDATE AS

SET NOCOUNT ON

/* * CASCADE UPDATES TO '[Tbl A]' */

IF UPDATE([Col C])

BEGIN

UPDATE t

SET t. [Col C]=i. [Col C]

FROM [Tbl A] t
inner join deleted d
ON d. [Col C]= t. [Col C]
inner join inserted i
on i.[Col C]=d.[Col C]

END[/code]

cant understand what you meant by last value of C. For each updation of C trigger fires and updates all records in A whose ColC value was deleted tables value with inserted tables value.Hope this is what you want

nandithab
Starting Member

6 Posts

Posted - 2008-02-12 : 07:42:08

what i meant is: in Table C say i have 4 rows .and the PK id initailly will be say 3,4,5,6.On updation this id will change to say 23,24,25,26.
Now the dependent table A should also update with respective id 23,24,25,26 .
The trigger which i created will update all rows of table A with the last row value of Table C.ie., 26 irrespective of their deleted values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources