• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

MS SQL Update Trigger

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
mcsween
Asked:
mcsween
2 Solutions
 
lcohanDatabase AnalystCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
mcsweenSr. Network AdministratorAuthor Commented:
Thanks for the help; this is exactly what I needed.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now