Using triggers to record table changes in SQL Server 2000

If you need to keep track of changes made to a table in SQL Server 2000, you can use triggers to record inserts, updates, and deletes automatically. This way, you don't need to keep the history up to date with your own code.

A trigger executes when data in the table changes.

The first step is to create a simple history (audit) table that will keep the changes:

CREATE TABLE AuditLog
(
    Id INT IDENTITY(1,1),
    EventTime DATETIME,
    EventType VARCHAR(10),
    UserName VARCHAR(50)
)

Next, create an UPDATE trigger:

CREATE TRIGGER trig_Update_Customer ON Customers
FOR UPDATE
AS
BEGIN
    INSERT INTO AuditLog
    (
        EventTime,
        EventType,
        UserName
    )
    VALUES
    (
        GETDATE(),
        'UPDATE',
        SYSTEM_USER
    )
END

This records the update time and current SQL Server user whenever rows in the Customers table are modified.

You can also create INSERT and DELETE triggers using the same approach.

SQL Server provides special inserted and deleted tables inside the trigger, which can be used to inspect changed rows.

SELECT * FROM inserted
SELECT * FROM deleted

This is useful for simple auditing and troubleshooting data changes.