How to Track T-SQL Updates


There is a field in a table that is either "0" or "1".  We make T-SQL script updates to this table, on occasion, in order to change the field to "1".

Is there a way to determine that,  through some sort of tracking mechanism, the table has been updated and when?

Thank you!

John EllisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
Pawan KumarDatabase ExpertCommented:
>>Is there a way to determine that,  through some sort of tracking mechanism, the table has been updated and when?
TRIGGER is one way but in that you case you have to design all your system manually. You need to create a table , using trigger you need to insert the data into the logging table..etc.. Note - triggers are very difficult to manage.

Another option is CHANGE DATA CAPTURE
In this you need to use Enable Change Data Capture for a Table.  With this SQL Server will handle everything for you.
Sample below...
-- =============  
-- Enable a Table for All and Net Changes Queries template   
-- =============  
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'MyTable',  
@role_name     = N'MyRole',  
@supports_net_changes = 1  

Open in new window

Another option is CHANGE TRACKING
Scott PletcherSenior DBACommented:
Éric Moreau is correct that you should use triggers.  The other methods involve far more complexity / difficultly in use and overhead than you need.  

However, the sample triggers in the link are horribly wrong and inaccurate for SQL Server.  Specifically, triggers using variables to load key values should never be used in SQL Server.  SQL Server fires a trigger only once per statement, no matter how many rows are deleted.  Using a variable would mean you would get only the "last" key value, even if 100 rows (or 1M rows) were UPDATEd.

To implement the trigger, first you'll need to create a table to hold the change history.  You'll want to include the column(s) that make up the key / identifying column(s) of the table.  Since I don't know the details of your table, I've made up 2 keys.  Adjust them as needed to match your actual table.

Then the trigger itself, to capture the previous column value, who made the change, the datetime of change (or more or less, depending on what you need/want).

Note: It's just a coincidence that change_type = 1 means the column was changed to 1.  The main thing is to use a code to indicate the change being tracked rather than using free-form text in a column.  That type of thing is *extremely* complex to parse thru later.

CREATE TABLE dbo.your_table_changes (
    key_col1 int NOT NULL,
    key_col2 int NOT NULL,
    change_date datetime NOT NULL DEFAULT GETDATE(),
    suser_sname nvarchar(128) NULL,
    previous_column_value varchar(10) NULL,
    change_type tinyint NOT NULL
CREATE TABLE dbo.your_table_change_types (
    change_type tinyint NOT NULL PRIMARY KEY,
    description varchar(200) NOT NULL

INSERT INTO dbo.your_table_change_types VALUES(1, 'Column name changed to "1".')

ON dbo.your_table
IF UPDATE(column_name)
    INSERT INTO dbo.your_table_changes ( key_col1, key_col2, change_date,
        suser_sname, previous_column_value, change_type )
    SELECT  i.key_col1, i.key_col2, GETDATE(),
        SUSER_SNAME(), d.column_name, 1
    FROM inserted i
    INNER JOIN deleted d ON d.key_col1 = i.key_col1 AND d.key_col2 = i.key_col2
    WHERE i.column_name = '1' AND (d.column_name IS NULL OR d.column_name <> '1')
END /*IF*/
Pawan KumarDatabase ExpertCommented:
CHANGE DATA CAPTURE can be enabled for a single table. I have used it personally and it is not much complex but it all depends on what you need and what for. You can read more about audit methods.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
CDC is extremely complex to fully set up and use.  MS functions are so cumbersome and get so many errors I had to rewrite some of them.  I hope it works out for you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.