Creating an SQL trigger in SQL 2005 server

I have a table in SQL server 2005.   I would like to have the following scenario:
Whenever a new row is inserted into this table I would like to update another existing table.  One column of this table is to be updated with the value inserted into one of the original table's columns.

Example:
Table in which rows are inserted:

Status Table:
-----------------
ID
Status

Existing table to update:

Customers Table:
---------------------
ID
Name
LastName
Status

When a new row is added into the Status table (signalling status change), I would like the matching record's Status field in the Customer table to take on the value inserted in the Status table.

I would appreciate if you could post an appropriate T-SQL statement to enter into the trigger definition.

Thanks
CLoucasAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
CREATE TRIGGER Status_insert_trigger
ON Status
AFTER INSERT
AS
UPDATE Customers
SET Status = i.status
FROM Customers
INNER JOIN Inserted i ON i.ID = Customers.ID
0
 
AntonyDNCommented:
If the ID in both tables refers to the customer, then this should do it ....




UPDATE Customer
SET Customer.Status = StatusTable.Status 
FROM Customer
JOIN StatusTable 
ON Customer.ID = StatusTable.ID
WHERE StatusTable.ID = @@IDENTITY

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.