Update Primary key-Using Trigger

Question

question

Damus asked Feb 4, '10

Update Primary key-Using Trigger

Can we update the primary key of a table using a trigger in sql server 2005.

I want to update the primary key of another table if it is changed in the first table,

so i want to use the old value in the table to update its new value.Hence, kindly provide me that solution.

update primary-key

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Damus commented · Feb 05, 2010 at 03:40 AM

Thank you dportas it is working for primary key, but i am using composite primary key(different columns in first table) and another composite primary key(different column in the second) but one is unique in the two. Here, i was confused a bit,please notify that one to me that how can i?

0 ·

David 1 commented · Feb 06, 2010 at 06:49 AM

Assuming that same set of columns will have unique values in both tables then just add a UNIQUE constraint as well as a foreign key. (doesn't have to be the primary key). ALTER TABLE tbl2 ADD CONSTRAINT ck1 UNIQUE (col1, col2); ALTER TABLE tbl1 ADD CONSTRAINT fk1 FOREIGN KEY (col1,col2) REFERENCES tbl2 (col1,col2) ON UPDATE CASCADE; If you need more help then please post the CREATE TABLE statements, including keys so that we can understand the situation better.

0 ·

Answer 1 · 2010-02-04T04:27:26Z

David 1 answered Feb 4, '10

Yes you can, assuming the key in the target table is not an IDENTITY. IDENTITY columns can't be updated.

You don't need a trigger. I think the easiest way would be to create a foreign key constraint on the key that needs to be updated and use the ON UPDATE CASCADE option:

ALTER TABLE tbl1 ADD CONSTRAINT fk1
FOREIGN KEY (col) REFERENCES tbl2 (col) ON UPDATE CASCADE;

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question