By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Update table using triggers

P: 2
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?
Feb 13 '08 #1
Share this Question
Share on Google+
1 Reply


amitpatel66
Expert 100+
P: 2,367
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?
I think in your table structure you have made one small typo, as shown in bold below:

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

Am I right?
Feb 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.