Solved

MS SQL Update Trigger

Posted on 2014-03-26
3
489 Views
Last Modified: 2014-03-26
I have a simple one for any database guys out there.

I have the fields isActive (bit) and Inactive_Date (datetime) in the table users.

I need a trigger to update the Inactive_Date field with CURDATE() when the field isActive is updated to 0 (False).

There are circumstances where this field would already be 0 but updated to the same value (during imports).  I do not want Inactive_Date updated under these circumstances.  It should only happen when the field changes from 1 to 0.

SQL 2008 R2
0
Comment
Question by:mcsween
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 150 total points
ID: 39956160
This what you mean?

CREATE TRIGGER [dbo].[TU_TableName]    ON  [dbo].[TableName]
 AFTER UPDATE
AS
SET NOCOUNT ON;
BEGIN
IF UPDATE(isActive)-- AND DELETED.isActive = 1 AND INSERTED.isActive = 0
UPDATE [dbo].[TableName] SET Inactive_Date = GETDATE() -- CURDATE()
      FROM INSERTED i, DELETED d WHERE d.isActive = 1 AND i.isActive = 0;
END
GO
0
 
LVL 34

Accepted Solution

by:
ste5an earned 350 total points
ID: 39956247
Triggers in SQL Server are executed per statement, thus they must work on sets. E.g.

CREATE TRIGGER dbo.tr_YourTableName_U ON dbo.YourTableName
    AFTER UPDATE
AS
    SET NOCOUNT ON;
   
    IF UPDATE(IsActive)
        BEGIN
            UPDATE  T
            SET     InactiveDate = GETDATE()
            FROM    dbo.YourTableName T
                    INNER JOIN INSERTED I ON T.PrimaryKeyColumns = I.PrimaryKeyColumns
                    INNER JOIN DELETED D ON I.PrimaryKeyColumns = D.PrimaryKeyColumns
            WHERE   I.IsActive = 0
                    AND D.IsActive = 1;   
        END;

Open in new window

0
 
LVL 22

Author Closing Comment

by:mcsween
ID: 39956605
Thanks for the help; this is exactly what I needed.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

695 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