SQL 2005触发器教程，如何创建与使用？-建站教程-锦华智联科技

SQL Server 2005 触发器完整教程

什么是触发器？
触发器的类型
- DML 触发器 (数据操作语言触发器)
- DDL 触发器 (数据定义语言触发器)
创建 DML 触发器
- AFTER 触发器
- INSTEAD OF 触发器
重要的特殊表：inserted 和 deleted
管理触发器
- 查看触发器定义
- 禁用和启用触发器
- 删除触发器
进阶主题与最佳实践
- 递归触发器
- 事务管理
- 性能考虑
综合示例：实现审计日志

什么是触发器？

触发器是一种特殊的 存储过程，它不能被显式地调用，它在与数据表相关的特定事件（如 INSERT, UPDATE, DELETE）发生时 自动执行。

（图片来源网络，侵删）

可以把触发器想象成一个“守门员”或“警报系统”，当有人试图对数据进行修改时，触发器会自动被激活,执行一系列预设的操作。

核心特点：

自动执行：无需手动调用。
事件驱动：由 INSERT, UPDATE, DELETE 等 DML 语句或 CREATE, ALTER, DROP 等 DDL 语句触发。
与数据表紧密绑定：创建在特定的表上,是数据库对象的一部分。

触发器的类型

SQL Server 2005 主要支持两种类型的触发器。

A. DML 触发器 (Data Manipulation Language Trigger)

这是最常用的触发器类型，在数据表上进行 INSERT, UPDATE, DELETE 操作时触发。

（图片来源网络，侵删）

SQL Server 2005 引入了两种强大的 DML 触发器模式：

AFTER 触发器
- 执行时机：在指定的 DML 操作（INSERT, UPDATE, DELETE）成功执行之后才触发。
- 作用：通常用于执行一些辅助性的、基于数据变更后的操作，审计日志、数据同步、发送通知、更新统计信息等。
- 一个表上可以定义多个 AFTER 触发器,它们会按照创建时的顺序执行。
INSTEAD OF 触发器
- 执行时机：替代原本的 DML 操作，它不会执行 INSERT, UPDATE, DELETE 语句本身,而是执行触发器内部定义的逻辑。
- 作用：通常用于在视图上执行 INSERT, UPDATE, DELETE 操作（因为视图本身可能不支持这些操作）,或者用于执行比默认操作更复杂的逻辑。
- 一个表或视图上，对每个 DML 操作只能定义一个 INSTEAD OF 触发器。

B. DDL 触发器 (Data Definition Language Trigger)

触发事件：在执行 DDL 语句（如 CREATE TABLE, ALTER VIEW, DROP PROCEDURE 等）时触发。
作用范围：作用于整个数据库或服务器级别。
主要用途：用于管理数据库架构，防止删除关键表、记录数据库对象的变更历史、在特定事件发生时发送警报等。

创建 DML 触发器

让我们通过一个具体的例子来学习，假设我们有一个员工表 Employees。

准备示例表：

-- 创建员工表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATETIME
);
-- 插入一些测试数据
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES 
(1, 'John', 'Doe', 'IT', 60000.00, '2025-01-15'),
(2, 'Jane', 'Smith', 'HR', 55000.00, '2025-05-20'),
(3, 'Peter', 'Jones', 'IT', 75000.00, '2025-03-10');

A. 创建 `AFTER` 触发器

目标： 当向 Employees 表中插入新员工时，自动在控制台打印一条消息,并记录到日志表中。

步骤 1：创建日志表

CREATE TABLE EmployeeAuditLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    Action NVARCHAR(10),
    EmployeeID INT,
    ActionTime DATETIME,
    Details NVARCHAR(255)
);

步骤 2：创建 AFTER INSERT 触发器

CREATE TRIGGER trg_Employee_AfterInsert
ON Employees
AFTER INSERT -- 在INSERT操作成功后触发
AS
BEGIN
    -- 声明一个变量来存储消息
    DECLARE @message NVARCHAR(255);
    -- 从inserted表中获取新插入的数据
    -- inserted是一个逻辑表，包含了所有被INSERT操作影响的行
    SELECT @message = '新员工 ' + i.FirstName + ' ' + i.LastName + ' 已加入 ' + i.Department + ' 部门。'
    FROM inserted i;
    -- 打印消息到SQL Server Management Studio (SSMS)的消息窗口
    PRINT @message;
    -- 将操作记录到审计日志表
    INSERT INTO EmployeeAuditLog (Action, EmployeeID, ActionTime, Details)
    SELECT 'INSERT', EmployeeID, GETDATE(), '新员工入职'
    FROM inserted;
END
GO

步骤 3：测试触发器

-- 执行一个INSERT操作
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES (4, 'Mary', 'Williams', 'Finance', 65000.00, '2025-11-01');

你会看到：

消息窗口会显示：新员工 Mary Williams 已加入 Finance 部门。
EmployeeAuditLog 表中会新增一条记录。

B. 创建 `INSTEAD OF` 触发器

目标： 我们希望不允许直接删除 Employees 表中的员工，而是将他们标记为“已离职”（通过增加一个 IsTerminated 列），我们可以使用 INSTEAD OF DELETE 触发器来实现。

步骤 1：修改 Employees 表

ALTER TABLE Employees
ADD IsTerminated BIT DEFAULT 0; -- 0表示在职，1表示离职
GO

步骤 2：创建 INSTEAD OF DELETE 触发器

CREATE TRIGGER trg_Employee_InsteadOfDelete
ON Employees
INSTEAD OF DELETE -- 不执行DELETE，而是执行这里的逻辑
AS
BEGIN
    PRINT '执行软删除逻辑，将员工标记为离职。';
    -- 将要删除的员工标记为离职
    -- 注意：我们不能直接操作原表，而是要操作被影响的行，这些行在deleted表中
    UPDATE Employees
    SET IsTerminated = 1
    FROM Employees e
    INNER JOIN deleted d ON e.EmployeeID = d.EmployeeID;
    -- 记录到审计日志
    INSERT INTO EmployeeAuditLog (Action, EmployeeID, ActionTime, Details)
    SELECT 'DELETE', EmployeeID, GETDATE(), '员工被标记为离职'
    FROM deleted;
END
GO

步骤 3：测试触发器

-- 尝试删除一个员工
DELETE FROM Employees WHERE EmployeeID = 1;

你会看到：

消息窗口显示：执行软删除逻辑，将员工标记为离职。
检查 Employees 表，员工 John Doe 的 IsTerminated 列值变为 1,但他并没有从表中消失。
EmployeeAuditLog 表中记录了一次“DELETE”操作。

重要的特殊表：`inserted` 和 `deleted`

在 DML 触发器中，SQL Server 提供了两个特殊的、临时的内存表：

inserted 表：
- 当执行 INSERT 或 UPDATE 操作时触发。
- 它包含了 新插入的行 或 更新后的行 的数据。
- 对于 INSERT，inserted 表包含所有新行。
- 对于 UPDATE，inserted 表包含更新后的新值。
deleted 表：
- 当执行 UPDATE 或 DELETE 操作时触发。
- 它包含了 被删除的行 或 更新前的旧行 的数据。
- 对于 DELETE，deleted 表包含所有被删除的行。
- 对于 UPDATE，deleted 表包含更新前的旧值。

操作	`inserted` 表内容	`deleted` 表内容
`INSERT`	新数据	空
`UPDATE`	新数据	旧数据
`DELETE`	空	被删除的数据

管理触发器

A. 查看触发器定义

-- 查看指定触发器的定义文本
EXEC sp_helptext 'trg_Employee_AfterInsert';
-- 查看指定表上的所有触发器信息
EXEC sp_helptrigger 'Employees';

B. 禁用和启用触发器

如果暂时不想让触发器生效，但又不想删除它,可以禁用它。

-- 禁用触发器
DISABLE TRIGGER trg_Employee_AfterInsert ON Employees;
-- 执行INSERT，此时不会触发
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES (5, 'David', 'Brown', 'IT', 70000.00, '2025-01-10');
-- 重新启用触发器
ENABLE TRIGGER trg_Employee_AfterInsert ON Employees;
-- 再次执行INSERT，触发器会生效
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES (6, 'Eve', 'Davis', 'Marketing', 58000.00, '2025-02-15');

C. 删除触发器

DROP TRIGGER trg_Employee_InsteadOfDelete ON Employees;

进阶主题与最佳实践

A. 递归触发器

SQL Server 2005 支持两种递归：

间接递归：
- 触发器 A 执行一个操作，该操作触发了触发器 B。
- 触发器 B 执行一个操作，该操作又触发了触发器 A。
- 这会形成一个链式反应。
直接递归：
- 触发器执行一个 DML 操作，该操作又触发了同一个触发器。

控制递归：

-- 查看递归是否启用
DBCC TRACERON(3651); -- 1表示启用，0表示禁用
-- 禁用直接递归
ALTER DATABASE YourDatabaseName
SET RECURSIVE_TRIGGERS OFF;

警告： 递归触发器容易导致无限循环和性能问题,应谨慎使用。

B. 事务管理

触发器通常在事务中运行，如果触发器中的操作失败，整个 DML 操作（INSERT, UPDATE, DELETE）也会回滚。

CREATE TRIGGER trg_SafeInsert
ON Employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        -- 触发器中的业务逻辑
        PRINT '开始执行触发器事务...';
        -- 模拟一个错误
        -- IF (SELECT COUNT(*) FROM inserted) > 0
        --    RAISERROR('触发器内发生错误！', 16, 1);
        -- 正常操作
        PRINT '触发器事务执行成功。';
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        PRINT '触发器事务失败，已回滚。';
        PRINT '错误信息: ' + ERROR_MESSAGE();
        -- 将错误重新抛出，让外层调用者也能知道
        THROW; -- SQL Server 2005 使用 RAISERROR，但 THROW 更现代
    END CATCH
END

C. 性能考虑

避免复杂逻辑：触发器中的代码应尽量简洁高效。
减少锁的持有时间：在触发器中避免耗时操作（如网络调用、复杂计算），这会延长锁的持有时间,影响并发性能。
谨慎使用游标：尽量使用基于集合的操作（SET 操作）代替逐行处理的游标。
注意递归：如前所述,递归是性能杀手。

综合示例：实现审计日志

这是一个更完整的审计日志触发器示例，它会记录 INSERT, UPDATE, DELETE 操作,并捕获变更前后的完整数据。

-- 创建一个更详细的审计日志表
CREATE TABLE EmployeeDetailedAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    ActionType CHAR(6), -- 'INSERT', 'UPDATE', 'DELETE'
    EmployeeID INT,
    ActionUser NVARCHAR(128), -- 执行操作的用户
    ActionTime DATETIME,
    OldValues NVARCHAR(MAX), -- JSON格式的旧值
    NewValues NVARCHAR(MAX)  -- JSON格式的新值
);
-- 创建一个综合的AFTER触发器
CREATE TRIGGER trg_Employee_ComprehensiveAudit
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    -- 处理 INSERT 操作
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO EmployeeDetailedAudit (ActionType, EmployeeID, ActionUser, ActionTime, OldValues, NewValues)
        SELECT 
            'INSERT', 
            i.EmployeeID, 
            SUSER_SNAME(), 
            GETDATE(),
            NULL, -- INSERT没有旧值
            -- 这里简化处理，实际项目中可以用XML或JSON聚合字段
            (SELECT i.FirstName + ', ' + i.LastName + ', ' + i.Department FOR XML PATH(''))
        FROM inserted i;
    END
    -- 处理 DELETE 操作
    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        INSERT INTO EmployeeDetailedAudit (ActionType, EmployeeID, ActionUser, ActionTime, OldValues, NewValues)
        SELECT 
            'DELETE', 
            d.EmployeeID, 
            SUSER_SNAME(), 
            GETDATE(),
            -- 简化处理旧值
            (SELECT d.FirstName + ', ' + d.LastName + ', ' + d.Department FOR XML PATH('')),
            NULL -- DELETE没有新值
        FROM deleted d;
    END
    -- 处理 UPDATE 操作
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO EmployeeDetailedAudit (ActionType, EmployeeID, ActionUser, ActionTime, OldValues, NewValues)
        SELECT 
            'UPDATE', 
            i.EmployeeID, 
            SUSER_SNAME(), 
            GETDATE(),
            -- 简化处理旧值
            (SELECT d.FirstName + ', ' + d.LastName + ', ' + CAST(d.Salary AS NVARCHAR) FOR XML PATH('')),
            -- 简化处理新值
            (SELECT i.FirstName + ', ' + i.LastName + ', ' + CAST(i.Salary AS NVARCHAR) FOR XML PATH(''))
        FROM inserted i
        INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID;
    END
END
GO

测试：

-- 插入
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate)
VALUES (7, 'Frank', 'Miller', 'Sales', 52000.00, '2025-03-01');
-- 更新
UPDATE Employees SET Salary = 78000 WHERE EmployeeID = 3;
-- 删除 (软删除)
DELETE FROM Employees WHERE EmployeeID = 2;

然后查询 EmployeeDetailedAudit 表,可以看到完整的审计记录。

SQL Server 2005 的触发器是一个非常强大且灵活的工具，通过本教程,你应该已经掌握了：

触发器的基本概念和类型 (AFTER vs. INSTEAD OF)。
如何创建和管理 DML 触发器。
如何使用 inserted 和 deleted 表来捕获数据变更。
触发器的事务管理和性能注意事项。
如何构建一个实用的审计日志系统。

何时使用触发器：

数据完整性：确保跨表的数据一致性（一个表删除时，另一个表的相关数据也做相应处理）。
审计与日志：自动记录数据变更历史。
复杂业务逻辑：当业务规则无法通过约束和外键简单实现时。
自动化任务：如数据同步、发送邮件等。

何时避免使用触发器：

简单的数据验证：应优先使用 CHECK 约束。
引用完整性：应优先使用外键约束。
可能导致性能瓶颈的复杂操作。
逻辑过于复杂，难以维护。

掌握触发器是成为一名高级 SQL Server 开发人员或 DBA 的重要一步,希望这份教程对你有帮助！

SQL 2005触发器教程，如何创建与使用？