|
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.
(comments are locked)
|
|
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:
(comments are locked)
|
Your answer
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?
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.