Creating Update Triggers in SQL Server 2008 for Data Change Logging

Introduction

In database applications, tracking data changes is a common requirement. For example, when updating the name in the Employees table, it is necessary to log the old value for auditing purposes. SQL Server 2008's trigger functionality can automate this task.

Core Concepts

SQL Server triggers are a special type of stored procedure that automatically execute when specific events (such as INSERT, UPDATE, DELETE) occur. For UPDATE triggers, two pseudo-tables are available: Inserted and Deleted. The Inserted table contains the new values after the update, and the Deleted table contains the old values before the update.

Implementation Steps

Here is a complete example demonstrating how to create tables and a trigger to log updates.

create table Employees (id int identity, Name varchar(50), Password varchar(50))
create table Log (id int identity, EmployeeId int, LogDate datetime, OldName varchar(50))
go
create trigger Employees_Trigger_Update on Employees
after update
as
insert into Log (EmployeeId, LogDate, OldName) 
select id, getdate(), name
from deleted
go
insert into Employees (Name, Password) values ('Zaphoid', '6')
insert into Employees (Name, Password) values ('Beeblebox', '7')
update Employees set Name = 'Ford' where id = 1
select * from Log

In this example, first, the Employees and Log tables are created. Then, an AFTER UPDATE trigger is created for the Employees table. When an update occurs, it retrieves the old name from the Deleted table and inserts it into the Log table along with the current timestamp.

Advanced Discussion

Referring to other answers, the Inserted and Deleted tables can be joined to log both old and new values simultaneously. For example, modify the trigger to record ID, old value, and new value.

CREATE TRIGGER trgEmployeeUpdate
ON dbo.Employees AFTER UPDATE
AS 
   INSERT INTO dbo.LogTable(ID, OldValue, NewValue)
      SELECT i.ID, d.Name, i.Name
      FROM Inserted i
      INNER JOIN Deleted d ON i.ID = d.ID

This method is more flexible and allows logging changes to multiple columns.

Best Practices

When using triggers, be mindful of performance impacts, especially on large tables with frequent updates. Ensure that the log table is well-designed and periodically clean up old data. Test triggers to ensure correctness and efficiency.

Conclusion

With SQL Server triggers, automatic logging of data changes can be easily implemented. Understanding the Inserted and Deleted pseudo-tables is key, and with proper table design, various auditing needs can be met.