trigger to update

5 pts.
Tags:
SQL
Triggers
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The best solution is to not update the primary key value for either table. What result are you trying to get by updating the primary key. If you need to change the value of the primary key, then that column shouldn’t be the primary key.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following