Trigger/Stored Procedure help 1

[lewatkin]

I'm sure this will be easy for some of you, but I am pretty new to the trigger/stored procedure world. I have a date field in two tables, and an employee id field in two tables. The dates are associated with a primary key of employee id. I would like to create a trigger (or stored procedure) that if the date field in table 1 is changed, it would lookup the employee id and update the date field in the table 2 - not vice versa. The date field in table 2 will be disabled through the application. In an ideal situation, there would only be one, but this is a third party application that we cannot change and we are trying to "help ourselves" not to forget to update the date field in the second table. It is a SQL 2005 database.

If this is doable, what is the best method? Trigger, stored procedure, combination of the two? I know just enough about it to be dangerous. Thanks in advance for any assistance someone may provide.

Lee

 

[philhege]

You probably want a FOR UPDATE trigger on table1.

Read the date from the trigger table INSERTED (a logical table which is available only in trigger code) and update the the date in table2 for the matching employee ID.

See Books Online for trigger syntax. Also read up on the function UPDATE(), which lets you know which fields have been updated (diff) so you don't update the target table with the same information.

-------++NO CARRIER++-------

 

[lewatkin]

Sorry for the delayed response. I just wanted to say thanks for pointing me in a direction to get me started. The code I used is posted below.

ALTER TRIGGER [dbo].[Date_Update]
ON [dbo].[staff_certs]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE [dbo].[staff_main]
SET certification_expire = expire_date, certification_rcvd = certification_date
FROM dbo.staff_main
INNER JOIN dbo.staff_certs ON
dbo.staff_main.staff_id = dbo.staff_certs.staff_id
WHERE certification_type = 'OFFICE'

END


It's probably not perfect - but hey, it works

Thanks again!
Lee