Track Field Change SQL 2008

Posted on 2014-08-12
Last Modified: 2014-08-12
This one should be easy. I've done a fair bit of dabbling in SQL with standard statements (select, insert...), views, and even a stored proc or 2, but now I need a bit more.

What I need is to watch a field in a table, any time that changes write an insert into another table with that effective date.

Our front office system doesn't track a Vehicle's cost center effective date, so I thought I could use a trigger to do it myself. The destination table is stupid simple 2 fields, Unit and a datetime stamp. I just have never done this before and don't know where to start.  I find database triggers under Programmability in Management studio, but when I right click I don't get an option for new.

We are on SQL 2005.
Question by:bhieb
    LVL 34

    Accepted Solution

    Aircode so take with a grain of salt...

    INSERT INTO DestinationTable (Unit, DateTimeStamp)
    FROM Inserted AS I
    LEFT OUTER JOIN deleted AS D
       ON I.SourceTableKey = D.SourceTableKey
    WHERE D.SourceTableKey IS NULL   -- An insert was made so no deleted table exists
       OR I.Unit <> D.Unit                         -- An update was made so just check that the specific column was changed

    Open in new window


    Author Comment

    Thanks. I had to tweak it some but works great.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    734 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

    20 Experts available now in Live!

    Get 1:1 Help Now