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?
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?
Comment