SqlServer——触发器

　　一：触发器基本知识 

1、首先必须明确以下几点：

• 触发器是一种特殊的存储过程，但没有接口（输入输出参数），在用户执行Inserted、Update、Deleted 等操作时被自动触发；
• 当触发的SQL语句不是显式事务的一部分时，SqlServer自动将引发触发器的SQL语句和触发器作为一个隐式事务， rollback tran 可以回滚引发触发器的SQL语句和触发器从而达到取消用户操作的目的；
• 当触发的SQL语句是显式事务的一部分时，将撤销从最外层 begin tran 开始的所有操作；
• 虽然定义在特定的表上，但触发器可以引用其它表中的列，所以触发器通常用来实现对表实施复杂的完整性约束和强制业务规则，它是一种高级约束。可以定义比用check约束更为复杂的约束。例如，无法用约束实现的强制完整性规则，审核，维护不规范数据。

2、触发器的 Inserted 表和 Deleted 表

　　这两个表属于临时表，前面所说触发器是一个特殊的存储过程，它是一段针对用户操作的数据执行的代码。所以为了能够获取用户操作的数据，就提供了这两个表用于保存用户操作的数据。

　　这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行完成后﹐与该触发器相关的这两个表也被删除。Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。 Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。用户可以用SELECT语句查询这两个临时表，但不允许进行修改。如表

对表的操作	Inserted	Update	Deleted
Inserted 表	插入的新数据	更新后的数据	X
Deleted 表	X	更新前的数据	删除的数据

 

 

 

Inserted 表和 Deleted 表没有索引，因此当表内数据较多且频繁引用表内数据时，应当将数据保存到一个临时表，为临时表创建索引。 

3、触发器的分类　　

根据 SqlServer 数据库是否 允许用户对永久表操作 分为 Instead of  触发器 和 After 触发器。

• Instead of 触发器：也称为替代触发器。定义该类触发器后 SqlServer 不允许用户对永久表操作，SqlServer 数据库仅仅是按照用户思维将修改的数据直接保存到 Inserted、Deleted 临时表中，而不是SQL数据库的永久表。只能通过触发器内部的代码利用Inserted 、Deleted 临时表中的数据对永久表表操作，即相当于只执行触发器代码，而用户提交的代码仅是将相关数据提交给触发器处理。

　　执行顺序: (1)根据用户提交的 DML 操作（ insert、update、delete ），将意欲操作的相关数据填充到 Inserted 表和 Deleted 表，但不会对SQL物理表进行修改;(2)执行 Instead of  触发器内容。

　　以用户的 update 操作为例，首先获取永久表中相关数据的副本保存到 Deleted 表，对副本数据执行update操作，将结果保存到 Inserted 表，而此时永久表没有任何变化，触发器开始执行，如果触发器没有对永久表操作的相关代码，那么用户的操作将无法作用到永久表。

• After 触发器：可由 DDL操作或 DML操作执行触发。该触发器按语句触发而不是按行触发，即一条语句不管影响了多少行，After 触发器只能触发一次；如果触发After触发器的 SQL语句违反了约束，则不触发After触发器；而触发 Instead of  触发器的 SQL语句并未真正修改SQL数据库的物理表，也就没有进行约束检查，可以触发 Instead of  触发器，因此Instead of  触发器可以对约束进行一些预处理。

　　执行顺序: (1)执行用户提交的 DML、DML操作，对实际的物理表做出修改，约束检查通过后将相关数据填充到 Inserted 表和 Deleted 表;(2) 执行 After 触发器 内容。

区别如表：　　

区别	作用对象	触发器数量	执行顺序
Instead of 触发器	表、DML 操作	1个/一种操作	填充 Instead、deleted 表→执行触发器
After 触发器	表、视图、DML 操作、DDL操作	多个	执行用户对永久表的操作→填充 Instead、deleted 表→执行触发器触发器

 

 

 

After 触发器：一种操作可以有多个 After  触发器，使用sp_settriggerorder存储过程标记最先执行和最后执行的触发器；

4、受影响的行数。

 如下语句：

　　select @c1=c1,@c2=c2,@c3=c3 from Inserted

　　如果并没有一行受到触发 触发器 的SQL语句的影响，那么变量@c1、@c2、@c3的值不会改变，仍然是原来的值，而当有多个行受到影响时，每一行都会对变量值进行修改，到最后，变量值只是最后一行的值。因此要先判断受影响的行数。

     @@rowcount 函数 保存着上一条语句受影响的行数，或者用 select @count=count（1） from Inserted 来判断Inserted 表中的行数。

二：编程实例　　

首先数据库中有这两个表

 

　　　　

 　　　　　表一：student_cj　　　　　　　　　　　　　　　　　　　　　　表二：student　　

 

1、after 触发器

　　例1：创建如下触发器：

create trigger trgafter
　　on dbo.student
　　for delete　　--for 等同于 after 
as
　　print ' AFTER 触发器'
　　select * from student_cj where name in(select name from deleted)　　--(1)
　　print '触发器有了用武之地'
　　select * from deleted　　--(2)
　　delete from student_cj where name in(select name from deleted)　　--(3)
　　print '触发器替你完成了额外任务'
　　select * from student_cj　　--(4)

 

执行SQL语句   delete from student where name ='小龙'

结果如图：

正如上面所言，SqlServer数据库首先将用户提交的SQL语句 delete from student where name ='小龙' 执行，将其删除的数据保存到 deleted 表中。

例2：执行SQL语句   insert into student values('小龙','男','24')

　　创建如下触发器：

create trigger trgafter2
　　on dbo.student
　　for delete
as
　　print ' AFTER 触发器'
　　if (select count(1) from student_cj where name in(select name from deleted)) =0　　--当student_cj表中没有与要删除数据相关的数据时
　　begin
　　　　print '触发器有了用武之地'
　　　　select * from deleted
　　　　rollback tran　　　　　　　　　　--回滚，取消用户操作。
　　　　print '触发器替你完成了额外任务'
　　　　select * from student
　　end

 执行SQL语句delete from student where name ='小龙'，如图

 　　由于用户提交的SQL语句与触发器作为一个整体被SQL数据库视为隐式事务，而触发器中将事务回滚，取消了用户的操作，因此最终student 表依然保留了 '小龙' 的数据。

　　2、Instead of 触发器

 　　　　　　　　先执行代码：delete from student_cj where name='小龙'

例1：创建如下触发器

 

create trigger trginstead1
　　on dbo.student_cj
　　instead of insert　　--为 insert 操作创建 instead of 触发器
as
　　declare @c1 varchar(10) ,@c2 varchar(10),@c3 varchar(10),@count int=0　　
　　print ' instead of 触发器'
　　select @count =COUNT (1) from student_cj where name ='小龙'　　--获取姓名为 ‘小龙’ 的相关记录数量 
　　print '姓名为 小龙 的记录数为：' +cast(@count as varchar)
　　DECLARE My_Cursor CURSOR --定义游标
　　FOR (select * from inserted) --查出需要的集合放到游标中
　　OPEN My_Cursor; --打开游标
　　FETCH NEXT FROM My_Cursor into @c1 ,@c2,@c3 ; --读取第一行数据
　　WHILE @@FETCH_STATUS = 0
　　　　BEGIN
　　　　　　if @c3>=0 and @c3 <=100　　--成绩必须在0-100之间，否则置为0
　　　　　　　　insert into student_cj values (@c1 ,@c2,@c3)
　　　　　　else if @c3>100
　　　　　　　　begin
　　　　　　　　　　insert into student_cj values (@c1 ,@c2,0)
　　　　　　　　　　print @c3+'? 成绩不能超过 100 分， instead of 触发器已经将其置为 0 分'
　　　　　　　　end
　　　　　　else if @c3<=0
　　　　　　　　begin
　　　　　　　　　　insert into student_cj values (@c1 ,@c2,0)
　　　　　　　　　　print @c3+'? 成绩不能低于 0 分， instead of 触发器已经将其置为 0 分'
　　　　　　　　end
　　　　　　FETCH NEXT FROM My_Cursor into @c1 ,@c2,@c3; --读取下一行数据
　　　　END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
select @count =COUNT (1) from student_cj where name ='小龙'　　--再次获取姓名为 ‘小龙’ 的相关记录数量 
print '姓名为 小龙 的记录数为：' +cast(@count as varchar)

 

再执行如下代码: insert into student_cj values('小龙','语文','12'),('小龙','语文','200')   select * from student_cj order by name

 　　　　　　　　结果如图：

　　　　　　　　　　　

　　如图，第一次获取姓名为 ‘小龙’ 的相关记录数量时，左图显示数量为0，说明触发 instead of 触发器的代码对数据库的修改只是作用于临时表而不是真正的物理表，第二次获取姓名为 ‘小龙’ 的相关记录数量时，左图显示数量为2，而这个是触发器根据临时表的数据进行了自定义的约束处理后对数据库物理表修改的结果。

　　当然，这种约束用check约束会更简单，这里只是简单的介绍 instead of触发器的原理。