第十四节：SQLServer触发器详解(概述、工作原理、应用)

一. 触发器概述

1. 什么是触发器

　　触发器是数据库用于保证数据完整性的一种方式，可以说它是"与表事件相关"的一种特殊的存储过程，它的执行不能由程序调用，也不能手动启用，而只能是通过事件来触发，比如当对表进行 Insert、Delete、Update操作的时候，就会激活触发器来执行，所以触发器通常用于保证数据完整性和一些业务约束规则等。

与存储过程的区别：

　　运行方式不同，触发器不能由execute语句调用，只能通过用户执行相关SQL的时候自动触发；而存储过程则需要用户、程序等显式的调用。

2. 触发器有什么好处/作用

　(1). 自动触发，无须调用。

　(2). 最主要的作用就是用于强制限制，它可以实现check约束所不能定义的，它可以使用其它表的数据来约束当前表，也就是说它能实现比check约束更为复杂的限制。

　(3). 可用于一些关联表数据的更新。

　(4). 可以跟踪状态，撤销违法操作，保证数据的准确性。

3. 触发器的分类

(1). 触发器通常分为三类：DML触发器、DDL触发器、登录触发器。

　A. DML(数据库操作语言）触发器：insert触发器、delete触发器、update触发器。（本节重点介绍）

　B. DDL(数据库定义语言) 触发器：以create,drop,alter开头的语句)事件时被激活使用，使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

　C. 登录触发器：登录触发器将为响应 LOGIN 事件而激发存储过程。

(2). 根据触发器执行的顺序分为： for、after、instead of。

　A. for/after：仅在触发 SQL 语句中指定的所有操作都已成功执行完后才被触发。

　B. instead of：触发 SQL 语句并不实际执行，走完instead of触发器的内容就结束。

(3). 按照特殊的执行方式又演变出来：嵌套触发器、递归触发器。

　A. 嵌套触发器：一个触发器在执行的时候调用了另外一个触发器，而这个触发器有接着调用了下一个，这样就形成了嵌套触发器。

　说明：

　　①. 嵌套触发器不一定形成环，最多允许嵌套32层，超过这个限制将被终止，并回滚整个事务。

　　②. 默认情况下，嵌套触发器是开启的，并且在同一个触发器事务中，一个嵌套触发器不能被触发两次。

　　③. 触发器是一个事务，嵌套中任何层次发生错误，都将整个回滚。

开启或关闭嵌套：

--禁用嵌套
exce sp_configure 'xxx_triggers',0;
--启用嵌套
exce sp_configure 'xxx_triggers',1;

　B. 递归触发器：是指一个触发器从其内部再一次激活该触发器，例如update操作激活的触发器内部还有一条数据表的更新语句，那么这个更新语句就有可能激活这个触发器本身，当然，这种递归的触发器内部还会有判断语句，只有一定情况下才会执行那个T_SQL语句，否则就成为无线调用的死循环了。

　SqlServer中的递归触发器包括两种：直接递归和间接递归。

　　①. 直接递归：触发器被触发后并执行一个操作，而该操作又使用一个触发器再次被触发。

　　②. 间接递归：触发器被触发并执行一个操作，而该操作又使另一个表中的某个触发器被触发，第二个触发器使原始表得到更新，从而再次触发第一个触发器。

默认情况下，递归触发器选项是禁用的。递归触发器最多只能递归16层，如果递归中的第16个触发器激活了第17个触发器，则结果与发布的rollback命令一样，所有数据都将回滚。

开启直接递归触发器的方式：

　　T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true；

　　可视化界面：数据库上点右键->属性->选项。

二. 工作原理

1. 从临时表的角度分析

　(1). 触发器触发的时候，会在内存中新建 inserted表和 deleted表，这两张表是只读的，不允许修改，触发器执行完，这两张表自动删除。

　(2). inserted表：存放插入的记录行和更新后的记录行。可以从该表中检查插入插入或更新的数据是否符合要求，从而决定是否回滚。

　(3). deleted表：存放删除的记录行和更新前的记录行。可以从该表中检查删除的数据是否符合要求，从而决定是否回滚。

2. 从执行流程的角度分析

　(1). 对于after/for类型的触发器：执行增/删/改SQL，执行完毕且生效于DB → 触发对应触发器 → 生成inserted表和deleted表，并插入对应的数据 → 走触发器内部的自己写的业务代码（这里主要对要执行的sql语句，进行一些限制，根据inserted和deleted表查询对应的数据，从而决定回滚、抛异常、还是放行） → 执行完毕。

　(2). 对于instead of 类型的触发器：执行增/删/改SQL，并未生效于DB→ 触发对应触发器 → 生成inserted表和deleted表，并插入对应的数据 →走触发器内部自己写的业务代码 → 执行完毕。（全程触发触发器的SQL并没有真的生效）

3. 从三种实际触发器插入临时表的角度分析

　(1). insert触发器：向inserted表中插入新行备份。

　(2). delete触发器：向deleted表中插入被删除行备份。

　(3). update触发器：向deleted表中插入更新前的记录行备份，向inserted表中插入更新后的记录行备份。

三. 应用

1. 基于after/for相关的触发器

(1). insert触发器

需求1：禁止插入年龄大于20的用户

create trigger insert_forbidden20
on UserInfor
after insert
as
begin
declare @userAge int;
select @userAge=(select userAge from inserted)
if(@userAge>20)
  begin
    RAISERROR('禁止插入年龄大于20的用户',1,1)  --显式抛出错误
    rollback transaction   --回滚
  end     
end;
--测试1
insert into UserInfor values('019','ypf',18,GETDATE()) --插入成功
insert into UserInfor values('018','ypf',21,GETDATE()) --插入失败

运行结果：

需求2：每增加1名学生，更新一下学生总数。

（先判断学生总数表中有没有记录，没有的话要插入一条，然后更新学生总数表中的唯一的一条记录中的stuCount字段）

create trigger trig_insert
on student
after insert
as
begin
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

(2). delete触发器

需求：角色表的中的数据永不删除

create trigger trig_Notdel
on RoleInfor
after delete
as
begin
    --insert into RoleInfor(roleName,roleDescirption)  select roleName,roleDescirption from deleted;  --部分字段恢复
    insert into Roleinfor select * from deleted; --全字段恢复
end;
-- 测试
delete from RoleInfor where id =(select top(1) id from RoleInfor);

运行结果：

需求2：将删除的数据输出来

create trigger trig_del1
on UserInfor
after delete
as
begin
    select * from deleted
end;
-- 测试
delete from UserInfor where id =(select top(1) id from UserInfor);

运行结果：

(3). update触发器

需求：输出更新UserInfor表中的userAge的前后的数据

create trigger trig_update
on UserInfor
after update
as
begin
    select userAge as '修改前的年龄' from deleted;
    select userAge as '修改后的年龄' from inserted;
end;
-- 测试
update UserInfor set userAge=110 where id =(select top(1) id from UserInfor);

运行结果：

2. 基于instead of相关的触发器

(1). insert触发器

需求1：创建触发器,禁止插入年龄大于20的用户

create trigger insert_forbidden20
on UserInfor
instead of insert
as
begin
    declare @userAge int;
    select @userAge=(select userAge from inserted)
    if(@userAge>20)
    begin
        select '插入年龄错误' as '失败原因'
    end    
    else
    begin
        insert into UserInfor select * from inserted
    end 
end;
--测试1
insert into UserInfor values('019','ypf',18,GETDATE())  --插入成功
insert into UserInfor values('018','ypf',21,GETDATE())  --插入失败

运行结果：

(2). delete触发器

需求1：角色表的数据永不删除

create trigger trig_Notdel
on RoleInfor
instead of delete
as
begin
    --select * from deleted;       --这里能查出来要删除的数据（说明了instead of类型的触发器，虽然不执行sql语句，但先建立deleted表,该表已经被插入了对应的数据了）
    RAISERROR('该表禁止插入数据',1,1)  --这里抛了一个异常，就算什么不写，也插入不进去
end;

运行结果：

需求2：将删除的数据输出来

（这里能查出来要删除的数据（说明了instead of类型的触发器，虽然不执行sql语句，但先建立deleted表,该表已经被插入了对应的数据了）

create trigger trig_del1
on UserInfor
instead of delete
as
begin
    select * from deleted
end;

-- 测试
delete from UserInfor where id =(select top(1) id from UserInfor);

运行结果：

(3). update触发器

测试：instead of不执行sql，deleted和inserted表中是否会有对应规则的数据。
测试结果：有！

需求：输出更新User表前后的数据userAge

--需求：输出更新User表前后的数据userAge
create trigger trig_update
on UserInfor
instead of update
as
begin
    select userAge as '修改前的年龄' from deleted;
    select userAge as '修改后的年龄' from inserted;
end;
-- 测试
update UserInfor set userAge=110 where id =(select top(1) id from UserInfor);

测试结果：

3. 管理触发器

(1). 查看相关触发器

--1. 查看数据库中所有的触发器
select * from sysobjects where xtype='TR';
-- 查看触发器的内容
exec sp_helptext 'trig_del1';
-- 查看某张表的所有触发器
exec sp_helptrigger UserInfor

(2). 启用和禁用触发器

-- 启用UserInfor表上的触发器
alter table UserInfor enable trigger insert_forbidden20;

-- 启用UserInfor表上的所有触发器
alter table UserInfor enable trigger ALL;

-- 禁用UserInfor表上的触发器
alter table UserInfor disable trigger insert_forbidden20;

-- 禁用UserInfor表上的所有触发器
alter table UserInfor disable trigger ALL;

(3). 修改触发器

-- 把触发器的内容改为删除的时候显示删除的数据了
alter trigger trig_Notdel
on RoleInfor
after delete
as
begin
     select roleName,roleDescirption,addTime from deleted
end;

(4). 删除触发器

--删除下面两个触发器
drop trigger insert_forbidden20,trig_del1;

先判断后删除：

if (exists (select * from sysobjects where xtype='TR' and name='insert_forbidden20'))
    drop trigger insert_forbidden20;
if (exists (select * from sysobjects where xtype='TR' and name='trig_Notdel'))
    drop trigger trig_Notdel;
if (exists (select * from sysobjects where xtype='TR' and name='trig_del1'))
    drop trigger trig_del1;
if (exists (select * from sysobjects where xtype='TR' and name='trig_Notdel'))
    drop trigger trig_Notdel;
if (exists (select * from sysobjects where xtype='TR' and name='insert_forbidden20'))
    drop  trigger insert_forbidden20;
if (exists (select * from sysobjects where xtype='TR' and name='trig_update'))
    drop  trigger trig_update;

作者 : Yaopengfei(姚鹏飞)
博客地址 : http://www.cnblogs.com/yaopengfei/
声明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
声明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。

posted @ 2020-04-23 16:16 Yaopengfei 阅读(4954) 评论(1) 编辑收藏举报

刷新页面返回顶部

奔跑的路上

广泛汲取，用心打磨，精益求精。

第十四节：SQLServer触发器详解(概述、工作原理、应用)

公告