SQL Express 2008 - table 2 column1 equals table1 column1

Question

I have two tables:

• table1 has staffid
• table2 has staffid

I want table2 staffid to be equal to table1 staffid

and when changes are made to table 1 staff id they need to change staffid in table2

How do I do this?

Answer 1

CREATE TRIGGER [dbo].[TestDb] ON [dbo].[Table1] AFTER UPDATE AS

BEGIN DECLARE @staffID int SELECT staffID = staffID FROM INSERTED

-- do something for update in table 2 END

You can use BEFORE UPDATE or AFTER UPDATE trigger to achieve your task

Here is the reference http://msdn.microsoft.com/en-us/library/ms189799.aspx

Answer 2

As your first requirement is table2 staffid should be to equal table1 staffid, you can define a foreign key constraint on table2 staff id as:

ALTER TABLE table2 WITH NOCHECK
ADD CONSTRAINT FK_staffid
FOREIGN KEY(staffid)
REFERENCES table1(staffid)
ON UPDATE CASCADE

Foreign key option ‘With check’ specifies that any existing data in the foreign key table [ie Table2 ] should be validated to conform to the constraint whereas ‘WITH NOCHECK’ setting specifies that existing data is not validated to conform to the new constraint.This option can make the creation process more efficient when you know that all existing data in Table2 already conforms to the constraint, but any non-conforming records will be ignored during the creation. However, during subsequent updates to the non-conforming row, the constraint will be enforced, resulting in an error.

Also second requirement is changes made to table1 staffid should be cascaded to staffid in table2 ‘ON UPDATE CASCADE’ option should be used.

Answer 3

If you want it to be done automatically, you need to use ON UPDATE CASCADE for the table2 staffid.