Solved

Conditionally updating a table value in a table trigger

Posted on 2008-06-24
1
170 Views
Last Modified: 2010-03-19
I have defined a trigger on a table which fires everytime a row is inserted.  When a row is inserted, I update a second table's 'status' column with the new row's status field as follows:

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

This is working fine.  I however have an additional requirement:  If the i.status value is 'Offline' then I would also like to update the Customers table 'Active' field to 'NO'.

How can I do this in my SQL code?

Thanks

Chris
0
Comment
Question by:CLoucas
1 Comment
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
Comment Utility
CREATE TRIGGER Status_insert_trigger
ON Status
AFTER INSERT
AS
UPDATE Customers
SET Status = i.status,
Active = CASE WHEN i.Status = 'Offline' THEN 'NO' ELSE Active END
FROM Customers
INNER JOIN Inserted i ON i.ID = Customers.ID
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now