?
Solved

MS SQL Update Trigger

Posted on 2014-03-26
3
Medium Priority
?
490 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 600 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 35

Accepted Solution

by:
ste5an earned 1400 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

752 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