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"/>