在数据库的日常操作中，经常需要跟踪数据的变化，例如何时、谁以及在表中插入、更新或删除了什么。这对于审计、调试或报告目的非常有用。一种实现这一功能的方法是使用触发器，它们是特殊的存储过程，会在响应某些事件时自动执行。本文将向展示如何在SQL Server表中创建和使用触发器来监控数据变化。
SQL Server中有三种类型的触发器：DML、DDL 和登录触发器。DML 触发器在 INSERT、UPDATE 或 DELETE 语句影响表或视图时触发。DDL 触发器在 CREATE、ALTER 或 DROP 语句影响数据库对象时触发。登录触发器在用户登录数据库时触发。
要创建触发器，需要使用 CREATE TRIGGER 语句，其语法如下：
CREATE TRIGGER trigger_name ON table_name AFTER | INSTEAD OF {INSERT | UPDATE | DELETE} AS {sql_statements}
其中 trigger_name 是触发器的名称，必须在数据库中唯一。table_name 是触发器关联的表或视图的名称。AFTER 关键字指定触发器应在触发操作之后执行，而 INSTEAD OF 关键字指定触发器应在触发操作之前执行。INSERT、UPDATE 和 DELETE 关键字指定激活触发器的数据操作类型。sql_statements 是定义触发器逻辑的语句。
例如，假设有一个名为 Employees 的表，包含以下列：EmployeeID、FirstName、LastName 和 Salary。希望创建一个触发器，将对该表的每次更改记录到另一个名为 Employees_Audit 的表中，该表包含以下列：AuditID、EmployeeID、Action、OldSalary、NewSalary 和 DateTime。
可以使用以下代码创建 DML触发器：
CREATE TRIGGER Employees_Trigger ON Employees AFTER INSERT, UPDATE, DELETE AS
BEGIN
-- 声明变量
DECLARE @AuditID int;
DECLARE @EmployeeID int;
DECLARE @Action varchar(10);
DECLARE @OldSalary decimal(18,2);
DECLARE @NewSalary decimal(18,2);
DECLARE @DateTime datetime;
-- 获取当前日期和时间
SET @DateTime = GETDATE();
-- 检查是否为 INSERT 操作
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
-- 设置操作为 'INSERT'
SET @Action = 'INSERT';
-- 获取插入的员工 ID 和工资
SELECT @EmployeeID = EmployeeID, @NewSalary = Salary FROM inserted;
-- 设置旧工资为 null
SET @OldSalary = NULL;
-- 将记录插入审计表
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END
-- 检查是否为 UPDATE 操作
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- 设置操作为 'UPDATE'
SET @Action = 'UPDATE';
-- 获取更新的员工 ID 和工资
SELECT @EmployeeID = i.EmployeeID, @OldSalary = d.Salary, @NewSalary = i.Salary FROM inserted i INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
-- 将记录插入审计表
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END
-- 检查是否为 DELETE 操作
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- 设置操作为 'DELETE'
SET @Action = 'DELETE';
-- 获取删除的员工 ID 和工资
SELECT @EmployeeID = EmployeeID, @OldSalary = Salary FROM deleted;
-- 设置新工资为 null
SET @NewSalary = NULL;
-- 将记录插入审计表
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END
END;
现在，每当在 Employees 表中插入、更新或删除记录时，触发器都会在 Employees_Audit 表中插入相应的记录，并包含相关信息。
要测试触发器，可以使用以下语句：
-- 插入新员工
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary) VALUES (101, 'John', 'Doe', 50000);
-- 更新现有员工的工资
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 101;
-- 删除现有员工
DELETE FROM Employees WHERE EmployeeID = 101;
这些语句的结果可以在 Employees_Audit 表中看到：
| AuditID | EmployeeID | Action | OldSalary | NewSalary | DateTime |
|---|---|---|---|---|---|
| 1 | 101 | INSERT | NULL | 50000.00 | 2023-08-07 12:20:15.123 |
| 2 | 101 | UPDATE | 50000.00 | 60000.00 | 2023-08-07 12:21:23.456 |
| 3 | 101 | DELETE | 60000.00 | NULL | 2023-08-07 12:22:34.789 |
如所见，触发器已成功记录了对 Employees 表的每次更改。
另一个示例：
CREATE TRIGGER trigger_name ON table_name AFTER [Update], [Insert], [Delete] AS
BEGIN
...
END