x

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.

more ▼

asked Feb 04 '10 at 01:19 AM in Default

Damus gravatar image

Damus
65 8 8 9

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?
Feb 05 '10 at 03:40 AM Damus

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.
Feb 06 '10 at 06:49 AM David 1
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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; 
more ▼

answered Feb 04 '10 at 04:27 AM

David 1 gravatar image

David 1
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x128
x34

asked: Feb 04 '10 at 01:19 AM

Seen: 2511 times

Last Updated: Feb 04 '10 at 01:19 AM