Solved

MS SQL Update Trigger

Posted on 2014-03-26
3
488 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 33

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL / Table Lock? 7 35
SQL Server 2014 Express vs Full - Service Pack question 5 43
Sorting a SQL script 5 33
Begin Transaction 12 21
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.
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…
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.

742 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