Solved

MS SQL Update Trigger

Posted on 2014-03-26
3
473 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
3 Comments
 
LVL 39

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 32

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 21

Author Closing Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

747 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

12 Experts available now in Live!

Get 1:1 Help Now