Update Triggers to update another table in the DB

tennytitansgeek

I am working on an update trigger that updates certain columns in table two when table one is updated.
Example If Column a, b, or c is updated in table 1, I need those to be updated in table 2- Column a, b, or c based on the unique identifier column (let's say column a in both tables are the ids). The other problem I have is that for both table there is also a colum D. In Table 1 column d can be = red, blue, or pink. If column d is = red or pink then column d in table 2 needs to be updated to red. If table 1, column d = blue, then column d in table 2 needs to be updated to blue.
Can someone please help me?

Dec 4 '08 #1

ck9663

Can you post what you have so far?

Dec 5 '08 #2

tennytitansgeek

CREATE TRIGGER tr_u_employee ON employee
FOR UPDATE AS

DECLARE @a INT, @b INT, @C INT, @factor INT, @value INT, @columns AS VARCHAR(200), @colname AS VARCHAR(100)

IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 70 > 0))
AFTER UPDATE
ON Employee
FOR EACH ROW



-- Update record into Users table
UPDATE Portsl.dbo.Users
( employee_id,
FirstName,
LastName,
Status )
VALUES
( :old.employee_id,
:new.FirstName,
:new.LastName,
:new.Status );

END;

Dec 5 '08 #3

ck9663

On update trigger, get the old values from deleted and the new value from inserted tables. These are logical tables created during the update process and only accessible inside the trigger.

Happy coding!

-- CK

Dec 5 '08 #4