... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2010-01-21

Simple SQL Server Table Change Tracking

The following SQL Server script demonstrates a simple technique for maintaining a log of the changes made to a table. The main idea is embodied in the emphasized code.

CREATE TABLE zot
( X INT
, Y INT
)
GO

CREATE TABLE changeLog
( id INT IDENTITY
, time DATETIME
, tableName NVARCHAR(255)
, change NVARCHAR(MAX)
)
GO

CREATE TRIGGER TR_zot ON zot FOR INSERT, UPDATE, DELETE AS BEGIN
  INSERT INTO changeLog
    SELECT GETDATE(), 'zot'
    , COALESCE((SELECT * FROM DELETED FOR XML AUTO), '')
    + COALESCE((SELECT * FROM INSERTED FOR XML AUTO), '')
END
GO

INSERT INTO zot VALUES (1, 2)
INSERT INTO zot VALUES (3, 4)
INSERT INTO zot SELECT 4, 5 UNION SELECT 6, 7
UPDATE zot SET X=999 WHERE X < 4
DELETE FROM zot WHERE X=999
DELETE FROM zot

GO

SELECT * FROM changeLog
GO

DROP TABLE zot
DROP TABLE changeLog
GO

The result of this script shows what the change log entries would look like:

id time tableName change
1 2010-01-21 10:57:11.0 zot <INSERTED X="1" Y="2"/>
2 2010-01-21 10:57:11.0 zot <INSERTED X="3" Y="4"/>
3 2010-01-21 10:57:11.0 zot <INSERTED X="6" Y="7"/><INSERTED X="4" Y="5"/>
4 2010-01-21 10:57:11.1 zot <DELETED X="3" Y="4"/><DELETED X="1" Y="2"/><INSERTED X="999"Y="4"/><INSERTED X="999" Y="2"/>
5 2010-01-21 10:57:11.1 zot <DELETED X="999" Y="4"/><DELETED X="999" Y="2"/>
6 2010-01-21 10:57:11.1 zot <DELETED X="6" Y="7"/><DELETED X="4" Y="5"/>

Blog Archive