SQL Server 触发器：原理、分类与实战创建示例

1. 触发器核心概念与分类

触发器（Trigger）是依附于表或视图的一种特殊存储过程，当执行 INSERT、UPDATE 或 DELETE 操作时由系统自动调用。它通常用于实现比 CHECK 约束更复杂的业务规则检查、数据审计或关联操作。

1.1 DML 触发器分类

DML（Data Manipulation Language）触发器分为两类：

• AFTER 触发器（之后触发）：必须等操作完成并成功写入后才会激活。仅支持在表上定义。
• INSTEAD OF 触发器（替代触发）：不执行原始的操作语句，而是完全替换为触发器内部的逻辑。既可在表上定义，也可在视图中定义。

1.2 DDL 触发器

DDL 触发器由数据库结构变更操作激发，如 CREATE、ALTER、DROP 等语句。

2. 逻辑虚表：INSERTED 与 DELETED

在触发器内部，系统会在内存中自动生成两张只读的临时表：Inserted 表和 Deleted 表。这两张表的结构与被操作的表一致。

3. 实战：创建与维护触发器

所有示例基于 T-SQL 语法。在实际业务中，触发器本身包含在一个事务中，若遇到错误可进行回滚。

第 1 步：创建 Insert 类型触发器

目的：在向主表插入数据时，自动在关联子表中生成对应记录。

-- 1. 清理环境
if (object_id('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
-- 2. 定义触发器
create trigger tgr_classes_insert
on classes
    for insert -- 指定为插入操作触发
as
    declare @id int, @name varchar(20), @temp int;
    -- 获取插入到新记录的 ID 和名称
    select @id = id, @name = name from inserted;
    -- 自定义业务逻辑计算
    set @name = @name + convert(varchar, @id);
    set @temp = @id / 2;
    -- 向附属表 student 插入数据
    insert into student values(@name, 18 + @id, @temp, @id);
    print '添加学生成功！';
go
-- 3. 测试数据
insert into classes values('5班', getDate());
select * from classes;
select * from student order by id;

第 2 步：创建 Delete 类型触发器（数据备份）

目的：删除数据前自动备份至历史表，防止误删。

-- 清理环境
if (object_id('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go

create trigger tgr_classes_delete
on classes
    for delete -- 指定为删除操作触发
as
    print '备份数据中……';
    if (object_id('classesBackup', 'U') is not null)
        -- 如果备份表存在，直接追加
        insert into classesBackup select name, createDate from deleted;
    else
        -- 如果不存在，先建表再插入
        select * into classesBackup from deleted;
    print '备份数据成功！';
go

-- 测试
delete classes where name = '5班';
select * from classesBackup;

第 3 步：创建 Update 类型触发器（级联修改）

目的：修改主键或关键字段时，同步更新关联表中的引用值。

if (object_id('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go

create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    -- 从 Deleted 表获取旧名称
    select @oldName = name from deleted;
    
    -- 检查是否有学生使用了该班级名进行关联
    if (exists (select * from student where name like '%'+ @oldName + '%'))
        begin
            -- 从 Inserted 表获取新名称
            select @newName = name from inserted;
            -- 执行级联更新
            update student 
            set name = replace(name, @oldName, @newName) 
            where name like '%'+ @oldName + '%';
            print '级联修改数据成功！';
        end
    else
        print '无需修改 student 表！';
go

-- 测试
update classes set name = '五班' where name = '5班';

第 4 步：列级触发器（字段限制）

利用 UPDATE([列名]) 函数判断具体哪个字段被修改，常用于禁止修改某些敏感字段。

if (object_id('tgr_classes_update_column', 'TR') is not null)
    drop trigger tgr_classes_update_column
go

create trigger tgr_classes_update_column
on classes
    for update
as
    -- 判断是否尝试修改 createDate 字段
    if (update(createDate))
        begin
            raisError('系统提示：班级创建时间不能修改！', 16, 11);
            rollback tran; -- 执行回滚，撤销操作
        end
go
-- 测试：尝试修改 createDate 将被阻止

4. Instead Of 触发器的特殊应用

INSTEAD OF 触发器不会执行引发它的操作，开发者必须手动在触发器内部编写对应的处理逻辑。这在无法直接对视图进行操作时非常有用。

4.1 拦截删除操作

if (object_id('tgr_classes_inteadOf', 'TR') is not null)
    drop trigger tgr_classes_inteadOf
go

create trigger tgr_classes_inteadOf
on classes
    instead of delete /*也适用于 insert, update*/
as
    declare @id int, @name varchar(20);
    -- 从 Deleted 表读取将要被删除的数据
    select @id = id, @name = name from deleted;
    
    print '开始执行安全删除...';
    -- 1. 先在子表中清理关联数据
    delete student where cid = @id;
    -- 2. 再删除主表数据
    delete classes where id = @id;
    print '安全删除完成';
go

-- 测试：此时执行 delete classes 会触发此逻辑而非直接删除

5. 常用管理命令与审计日志示例

5.1 触发器的生命周期管理

• 启用/禁用：ENABLE/DISABLE TRIGGER [名称] ON [对象];
• 修改脚本：使用 ALTER TRIGGER 而非重新创建。
• 查看源文本：使用 EXEC sp_helptext '触发器名称'。

5.2 综合审计日志记录

在一张表中同时处理 Insert、Update 和 Delete 的记录审计：

-- 创建日志表
create table log(
    id int identity(1, 1) primary key,
    action varchar(20),
    createDate datetime default getDate()
)
go

create trigger tgr_student_log
on student
after insert, update, delete
as
    if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
        begin insert into log(action) values('updated'); end
    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
        begin insert into log(action) values('inserted'); end
    else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
        begin insert into log(action) values('deleted'); end
go

-- 验证：分别执行增删改，查询 log 表观察结果。

以上就是 SQL Server 触发器创建的详细内容，更多关于 SQL Server 数据库高级特性的资料请关注本站其它相关文章！