Solved

Creating an SQL trigger in SQL 2005 server

Posted on 2008-06-18
2
161 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:CLoucas
2 Comments
 
LVL 2

Expert Comment

by:AntonyDN
ID: 21812782
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 21813278
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question