SqlServer基础之（触发器）
阅读⽬录
概念：
  触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的⼀种⽅法，它是与表事件相关的特殊的存储过程，它的执⾏不是由程序调⽤，也不是⼿⼯启动，⽽是由事件来触发，当对⼀个表进⾏操作（ insert，delete， update）时就会激活它执⾏。触发器经常⽤于加强数据的完整性约束和业务规则等。触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。
触发器和存储过程的区别:
  触发器与存储过程的区别是运⾏⽅式的不同，触发器不能执⾏EXECUTE语句调⽤，⽽是在⽤户执⾏Transact-SQL语句时⾃动触发执⾏⽽存储过程需要⽤户，应⽤程序或者触发器来显⽰地调⽤并执⾏。
⼀：触发器的优点
 1.触发器是⾃动的。当对表中的数据做了任何修改之后⽴即被激活。
 2.触发器可以通过数据库中的相关表进⾏层叠修改。
 3.触发器可以强制限制。这些限制⽐⽤CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引⽤其他表中的列。
⼆：触发器的作⽤
 触发器的主要作⽤就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的⼀致性，它能够对数据库中的相关表进⾏级联修改，提⾼⽐CHECK约束更复杂的的数据完整性，并⾃定义错误消息。触发器的主要作⽤主要有以下接个⽅⾯：
1. 强制数据库间的引⽤完整性
2. 级联修改数据库中所有相关的表，⾃动触发其它与之相关的操作
3. 跟踪变化，撤销或回滚违法操作，防⽌⾮法修改数据
4. 返回⾃定义的错误消息，约束⽆法返回信息，⽽触发器可以
5. 触发器可以调⽤更多的存储过程
三：触发器的分类
 SqlServer包括三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。
1.DML(数据操作语⾔,Data Manipulation Language)触发器
 DML触发器是⼀些附加在特定表或视图上的操作代码，当数据库服务器中发⽣数据操作语⾔事件时执⾏这些操作。SqlServer中的DML触发器有三种：
1. insert触发器：向表中插⼊数据时被触发；
2. delete触发器：从表中删除数据时被触发；
3. update触发器：修改表中数据时被触发。
当遇到下列情形时，应考虑使⽤DML触发器：
1. 通过数据库中的相关表实现级联更改
2. 防⽌恶意或者错误的insert、update和delete操作，并强制执⾏check约束定义的限制更为复杂的其他限制。
3. 评估数据修改前后表的状态，并根据该差异才去措施。
2.DDL(数据定义语⾔,Data Definition Language)触发器
 DDL触发器是当服务器或者数据库中发⽣数据定义语⾔(主要是以create,drop,alter开头的语句)事件时被激活使⽤，使⽤DDL触发器可以防⽌对数据架构进⾏的某些更改或记录数据中的更改或事件操作。
3.登录触发器
    登录触发器将为响应 LOGIN 事件⽽激发存储过程。与 SQL Server 实例建⽴⽤户会话时将引发此事件。登录触发器将在登录的⾝份
验证阶段完成之后且⽤户会话实际建⽴之前激发。因此，来⾃触发器内部且通常将到达⽤户的所有消息（例如错误消息和来⾃ PRINT 语句的消息）会传送到 SQL Server 错误⽇志。如果⾝份验证失败，将不激发登录触发器。
四：触发器的⼯作原理
触发器触发时：
1. 系统⾃动在内存中创建deleted表或inserted表；
2. 只读，不允许修改，触发器执⾏完成后，⾃动删除。
inserted表：
1. 临时保存了插⼊或更新后的记录⾏；
2. 可以从inserted表中检查插⼊的数据是否满⾜业务需求；
3. 如果不满⾜，则向⽤户发送报告错误消息，并回滚插⼊操作。　
deleted表：
1. 临时保存了删除或更新前的记录⾏；
2. 可以从deleted表中检查被删除的数据是否满⾜业务需求；
3. 如果不满⾜，则向⽤户报告错误消息，并回滚插⼊操作。
inserted表和deleted表对照：　
修改操作记录inserted表deleted表
增加(insert)记录存放新增的记录............
删除(deleted)记录..............存放被删除的记录
修改(update)记录存放更新后的记录存放更新前的记录
五：创建触发器
创建触发器的语法：
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
AS
T-SQL语句
GO
-
-with encryption 表⽰加密触发器定义的sql⽂本
--delete,insert,update指定触发器的类型
准备测试数据:
--创建学⽣表
create table student(
stu_id int identity(1,1) primary key,
stu_name varchar(10),
stu_gender char(2),
stu_age int
)
1.创建insert触发器
-
-创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
create table student_sum(stuCount int default(0));--创建存储学⽣⼈数的student_sum表
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
--测试触发器trig_insert-->功能是向student插⼊数据的同时级联插⼊到student_sum表中，更新stuCount
--因为是后触发器，所以先插⼊数据后，才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学⽣总⼈数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','⼥',30);
select stuCount 学⽣总⼈数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);
select stuCount 学⽣总⼈数 from student_sum;
执⾏上⾯的语句后，结果如下图所⽰：
既然定义了学⽣总数表student_sum表是向student表中插⼊数据后才计算学⽣总数的，所以学⽣总数表应该禁⽌⽤户向其中插⼊数据
--创建insert_forbidden，禁⽌⽤户向student_sum表中插⼊数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
RAISERROR('禁⽌直接向该表中插⼊记录，操作被禁⽌',1,1)--raiserror 是⽤于抛出⼀个错误
rollback transaction
end
--触发触发器insert_forbidden
insert student_sum (stuCount) values(5);
结果如下：
2.创建delete触发器
  ⽤户执⾏delete操作，就会激活delete触发器，从⽽控制⽤户能够从数据库中删除数据记录，触发delete触发器后，⽤户删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。
--创建delete触发器
create trigger trig_delete
on student
after delete
as
begin
select stu_id as 已删除的学⽣编号,stu_name stu_gender,stu_age
from deleted
end;
--执⾏⼀⼀条delete语句触发trig_delete触发器
delete from student where stu_id=1;
结果如下：
3.创建UPDATE触发器
  update触发器是当⽤户在指定表上执⾏update语句时被调⽤被调⽤，这种类型的触发器⽤来约束⽤户对数据的修改。update触发器可以执⾏两种操作：更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。
--创建update触发器
create trigger trig_update
on student
after update
as
begin
declare @stuCount int;
select @stuCount=count(*) from student;
update student_sum set stuCount =@stuCount;
select stu_id as 更新前学⽣编号,stu_name as 更新前学⽣姓名 from deleted
select stu_id as 更新后学⽣编号,stu_name as 更新后学⽣姓名 from inserted
end
-
-创建完成，执⾏⼀条update语句触发trig_update触发器
update student set stu_name='张飞' where stu_id=2;
4.创建替代触发器
  与前⾯介绍的三种after触发器不同，SqlServer服务器在执⾏after触发器的sql代码后，先建⽴临时的inserted表和deleted表，然后执⾏代码中对数据库操作，最后才激活触发器中的代码。⽽对于替代(instead of)触发器,SqlServer服务器在执⾏触发instead of 触发器的代码时，先建⽴临时的inserted表和deleted表，然后直接触发instead of触发器，⽽拒绝执⾏⽤户输⼊的DML操作语句。
--创建instead of 触发器
create trigger trig_insteadOf
on student
instead of insert
as
begin
declare @stuAge int;
select @stuAge=(select stu_age from inserted)
if(@stuAge >120)
select '插⼊年龄错误' as '失败原因'
end
创建完成，执⾏⼀条insert语句触发触发器trig_insteadOf
5.嵌套触发器介绍
　如果⼀个触发器在执⾏操作时调⽤了另外⼀个触发器，⽽这个触发器⼜接着调⽤了下⼀个触发器，那么就形成了嵌套触发器。嵌套触发器在安装时就被启⽤，但是可以使⽤系统存储过程sp_configure禁⽤和重新启⽤嵌套触发器。
  嵌套触发器不⼀定要形成⼀个环，它可以 T1->T2-&这样⼀直触发下去，最多允许嵌套 32 层。如
果嵌套的次数超过限制，那么该触发器将被终⽌，并回滚整个事务，使⽤嵌套触发器需要注意以下⼏点：
默认情况下，嵌套触发器配置选项是开启的。
在同⼀个触发器事务中，⼀个嵌套触发器不能被触发两次。
由于触发器是⼀个事务，如果在⼀系列嵌套触发器的任意层次中发⽣错误，则整个事物都将取消，⽽且所有数据回滚。
嵌套是⽤来保持整个数据库的完整性的重要功能，但有时可能需要禁⽤嵌套，如果禁⽤了嵌套，那么修改⼀个触发器的实现不会再触发该表上的任何触发器。在下述情况下，需要禁⽤嵌套触发器：
嵌套触发要求复杂⽽有理论的设计，级联修改可能会修改⽤户不想涉及的数据。
在⼀系列嵌套触发器中的任意点的时间修改操作都会触发⼀些触发器，尽管这时数据库提供很强的保护功能，但如果以特定的顺序更新表，就会产⽣问题。
使⽤下列语句禁⽤嵌套和再次启⽤嵌套：
--禁⽤嵌套
exce sp_configure 'nested triggers',0;
--启⽤嵌套
exce sp_configure 'nested triggers',1;
6.递归触发器
  触发器的递归是指⼀个触发器从其内部再⼀次激活该触发器，例如update操作激活的触发器内部还有⼀条数据表的更新语句，那么这个更新语句就有可能激活这个触发器本⾝，当然，这种递归的触发器内部还会有判断语句，只有⼀定情况下才会执⾏那个T_SQL语句，否则就成为⽆线调⽤的死循环了。
SqlServer中的递归触发器包括两种：直接递归和间接递归。
直接递归：触发器被触发后并执⾏⼀个操作，⽽该操作⼜使⽤⼀个触发器再次被触发。
间接递归：触发器被触发并执⾏⼀个操作，⽽该操作⼜使另⼀个表中的某个触发器被触发，第⼆个触发器使原始表得到更新，从⽽再次触发第⼀个触发器。
默认情况下，递归触发器选项是禁⽤的。递归触发器最多只能递归16层，如果递归中的第16个触发器激活了第17个触发器，则结果与发布的rollback命令⼀样，所有数据都将回滚。
我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。
间接递归：对 T1 操作从⽽触发 G1，G1 对 T2 操作从⽽触发 G2，G2 对 T1 操作从⽽再次触发 G1...
直接递归：对 T1 操作从⽽触发 G1，G1 对 T1 操作从⽽再次触发 G1...
设置直接递归：
默认情况下是禁⽌直接递归的，要设置为允许有两种⽅法：
T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true；
EM：数据库上点右键->属性->选项。
六：管理触发器　
1.查看触发器
(1).查看数据库中所有的触发器
--查看数据库中所有的触发器
use 数据库名
go
select * from sysobjects where xtype='TR'
sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name ⼀列，我们可以看到触发器名称。
(2).sp_helptext 查看触发器内容
use 数据库名
go
exec sp_helptext '触发器名称'
将会以表的样式显⽰触发器内容。
除了触发器外，sp_helptext 还可以显⽰规则、默认值、未加密的存储过程、⽤户定义函数、视图的⽂本。
(3).sp_helptrigger ⽤于查看触发器的属性
  sp_helptrigger 有两个参数：第⼀个参数为表名；第⼆个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显⽰指定表中所有类型触发器的属性。
use 数据库名
go
exec sp_helptrigger tableName
2.禁⽤启⽤触发器
  禁⽤:alter table 表名 disable trigger 触发器名称
  启⽤:alter table 表名 enable trigger 触发器名称
  如果有多个触发器，则各个触发器名称之间⽤英⽂逗号隔开。
  如果把“触发器名称”换成“ALL”，则表⽰禁⽤或启⽤该表的全部触发器。
3修改触发器
--修改触发器语法
ALTER TRIGGER  trigger_name
ON  table_name
[ WITH ENCRYPTION ]
FOR {[DELETE][,][INSERT][,][UPDATE]}
AS
sql_statement;sql存储过程实例
4.删除触发器
--语法格式:
DROP  TRIGGER  { trigger } [ ,...n ]
参数:
trigger: 要删除的触发器名称
n：表⽰可以删除多个触发器的占位符

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。