如何使用触发器“监控 SQL Server 表中的数据更改”

SQL Server 是一个强大且流行的关系数据库管理系统，支持各种数据操作。但是，有时您可能需要跟踪对数据的更改，例如在表中插入、更新或删除的时间、人员和内容。这对于审计、调试或报告目的很有用。

实现此目的的一种方法是使用触发器，触发器是响应特定事件自动执行的特殊存储过程。在本文中，我们将向您展示如何创建和使用触发器来监视 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 触发器

首先，您需要创建一个表来存储信息，我创建了一个带有字段的 Table：ID (int)、TableName (varchar-100)、Activity (varchar-20)。 Date_Time (datetime - 获取当前时间)

• ID: 主键
• TableName: 将要审计的表
• Activity: Insert / update / delete
• Data_Time: 存储对该表执行一个操作时的当前时间

CREATE TRIGGER Employees_Trigger
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Declare variables
DECLARE @AuditID int;
DECLARE @EmployeeID int;
DECLARE @Action varchar(10);
DECLARE @OldSalary decimal(18,2);
DECLARE @NewSalary decimal(18,2);
DECLARE @DateTime datetime;

-- Get the current date and time
SET @DateTime = GETDATE();

-- Check if it is an INSERT action
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'INSERT'
SET @Action = 'INSERT';

-- Get the inserted employee ID and salary
SELECT @EmployeeID = EmployeeID,
@NewSalary = Salary
FROM inserted;

-- Set the old salary to null
SET @OldSalary = NULL;

-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END

-- Check if it is an UPDATE action
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'UPDATE'
SET @Action = 'UPDATE';

-- Get the updated employee ID and salaries
SELECT @EmployeeID = i.EmployeeID, @OldSalary = d.Salary, @NewSalary = i.Salary
FROM inserted i
INNER JOIN deleted d
ON i.EmployeeID = d.EmployeeID;

-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END

-- Check if it is a DELETE action
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
-- Set the action to 'DELETE'
SET @Action = 'DELETE';

-- Get the deleted employee ID and salary
SELECT @EmployeeID = EmployeeID, @OldSalary = Salary
FROM deleted;

-- Set the new salary to null
SET @NewSalary = NULL;

-- Insert a record into the audit table
INSERT INTO Employees_Audit (EmployeeID, Action, OldSalary, NewSalary, DateTime)
VALUES (@EmployeeID, @Action, @OldSalary, @NewSalary, @DateTime);
END

END; 

现在，每当我们在 Employees 表中 insert、update 或 delete 一条记录时，触发器都会在 Employees_Audit 表中插入一条相应的记录，其中包含相关信息。

要测试触发器，我们可以使用以下语句

-- Insert a new employee
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES (101, 'John', 'Doe', 50000);

-- Update an existing employee's salary
UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 101;

-- Delete an existing employee
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 表所做的所有更改。

总之，触发器是 SQL Server 的一个有用的功能，它允许您监视表中数据的更改。您可以使用它们执行各种任务，例如审计、调试或报告。但是，在使用触发器时也应小心，因为如果设计和测试不当，它们会影响数据库的性能和完整性。

另一个例子

接下来，使用结构创建一个触发器

CREATE TRIGGER trigger_name
      ON table_name
      AFTER [Update] , [Insert], [Delete]
AS
BEGIN
     ...
END

• inserted 是当用户向表中插入任何行时存储的模板表。
• deleted 是当用户删除表中的任何行时存储的模板表。

感谢您阅读这篇文章。希望您觉得它有用且易于理解。如果您对 如何“使用触发器监视 SQL Server 表中的数据更改”有任何反馈或疑问，请在下面的评论中分享。我很乐意听取您的意见并进一步讨论这个话题。