Track changes to table

[gcole]

I need to track the changes made to a table so that I can report on what has changed. Any ideas?

 

[JamesLean]

One option is to set up triggers on the table you want to keep a history for.

You could also make all access to the DB thru stored procs and build the audit-keeping into those.

--James

 

[SQLSister]

Depends on the level of reporting you need to do. If you just want to know when the record last last changed add a field called ModificationDate and have an update trigger fill it in when ever a record is updated. If you want to see when the record was created and compare to when it was updated, then as a CreateDate field and give it a default value of GetDate().

If you need to know exactly what was changed you need an update trigger to copy the old record to an audit table. Then you can compare the two records in a SQL query to see the differences. Of course this won't work unless you have some kind of unique record identifier on the original table. It is also helpful to use one of the username functions to get the name of the person making the change too. The audit table can get right large, so you may want to only copy records where a few specific important fields have changed.

 

[kayek]

How can you make a trigger only go when a certain column is updated? The below trigger will go anytime the table is updated but I want it to go only what the name column in the table is updated. Also, how can I get what was in the column before and after it was changed.

CREATE TRIGGER tbl1_TrackUpdates ON [dbo].[Table2]
FOR INSERT, UPDATE
AS
update [dbo].[Table2] set [LastUpdatedDate] = getdate(), [LastUpdatedBy] = USER_NAME()

 

[JamesLean]

All this info is in Books Online. Look up CREATE TRIGGER, especially the IF UPDATE() syntax to check which columns were updated and the "inserted" and "deleted" pseudo-tables which contain the data before and after the action.

--James

 

[bs26]

Remember if the column has been updated with the same value, Update() will return true.

Before Update
Tbl1.Col1 = 45
Tbl1.Col2 = 'Test'
Tbl1.Col3 = 'Jubii...'

ex 1.
Update Tbl1
Set Col2 = 'Test',
Col3 = 'Hi'
Where Col1 = 45

Update(Col2) = true
Update(Col3) = true

ex 2.
Update Tbl1
Set Col3 = 'Hi'
Where Col1 = 45

Update(Col2) = false
Update(Col3) = true

 

[kayek]

Where are the books online?

What is the syntax for If UPDATE()

 

[bs26]

If UPDATE(Column)

ex.

Create Table Calls (
CallID int,
CallNumber varchar(20),
CallText varchar(100)
)

If UPDATE(CallText)

SQL Online Books
----------------
See under triggers ( creating/modifying )

IF UPDATE (column)

Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations.

UPDATE(column) can be used anywhere inside the body of the trigger.

{AND | OR}

Specifies another column to test for either an INSERT or UPDATE action.

column

Is the name of the column to test for either an INSERT or UPDATE action.