数据库 触发器

触发器时为执行业务规则和保持数据完整性而提供的一种机制，它可以在执行插入、更新，删除等操作的前后自动触发。触发器与存储过程类似，但是让不接收输入\输出参数没也不能被显式调用，只能有服务器事件自动触发，根据一起执行触发器语言的不同，可将其分为dml触发器和ddl触发器

1、dml触发器

      根据dml触发器发生的时间，编写触发器所使用的语言，可以分为after触发器、instead of触发器和clr触发器。alter触发器在执行insert、update或者delete语句操作之后，instead of 触发器和约束之后触发，instead of在处理约束前激发，因此可以再instead of 中使用其他语句来替代激发触发器的insert、update等语句，并且，还可以为基于一个或者多个基表的视图定义instead of 触发器，从而扩展视图可支持的更新类型，clr触发器可以是after触发器或者是instead of 触发器，也可以是ddl触发器，需要注意的是，在创建dml触发器时，不能使用一下语句：

alter database； create database；drop database；

load database load log； reconfigure

restore database restore log

（1）    after触发器

一个表中可以有多个after触发器，只要他们的名称不同即可。每个触发器只能应用于一个表，但是一个触发器可以应用于一个表的三个用户操作（update、insert、delete）

 下面的雨季创建了一个pritrigger，和一个detailtable，其中pritrigger表用于存放销售订单的编号和金额，detailtable表用于存放每笔订单的产品信息。为pritrigger表中的delete操作创建了一个名为pritrigger的触发器，当删除pritrigger表中的订单信息时，该触发器将删除detailtable表中该订单的产品信息

--创建主表，存放订单编号和金额

create table pritrigger

( orderid int identity(1,1),ordertotal money)

--明细表 存放订单中的产品信息

create table detailtable

( orderid int,

productid int,

productcount int,

price money)

 

insert into pritrigger values(100)

insert into pritrigger values(200)

insert into detailtable values(1,1,10,110)

insert into detailtable values(1,2,10,1000)

insert into detailtable values(2,2,10,1000)

 

create trigger pritrigger1

on pritrigger

after delete

as

              delete from detailtable

              where orderid in(

   select orderid from deleted

  )

          在定义触发器时，触发器名称在create trigger关键字之后，on字句指定要创建触发器的基表，after字句（也可以使用for来代替after关键字，二者功能相同）指定激活触发器的操作语句，可以同时指定多个操作语句。例如“after delete，insert”标示在对表执行delete、insert 语句时激活触发器。as关键字指定触发器执行什么样的操作、注意where条件中in字句中的deleted关键字。当从pritrigger表中删除行时，被删除的行会复制到一个名为deleted的临时内存表中，如果为表指定了一个insert语句时的触发器，则在想表中插入行时，新行将同时被添加到一个名为inserted的临时内存表中。如果为表指定了一个执行update语句时的触发器。由于更新事务类似于在删除操作之后执行插入操作。因此，旧的数据行被复制到deleted表中，然后新行复制到触发器表和inserted表中

deleted表和inserted表都是由数据库引擎自动创建和管理的，这些表的结构与定义触发器的基表的结构相同

（2）    instead of 触发器

  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

（3）    嵌套触发器

 

　如果一个触发器在执行操作时调用了另外一个触发器，而这个触发器又接着调用了下一个触发器，那么就形成了嵌套触发器。嵌套触发器在安装时就被启用，但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。

　　嵌套触发器不一定要形成一个环，它可以 t1->t2->t3...这样一直触发下去，最多允许嵌套 32 层。如果嵌套的次数超过限制，那么该触发器将被终止，并回滚整个事务，使用嵌套触发器需要注意以下几点：

默认情况下，嵌套触发器配置选项是开启的。

在同一个触发器事务中，一个嵌套触发器不能被触发两次。

由于触发器是一个事务，如果在一系列嵌套触发器的任意层次中发生错误，则整个事物都将取消，而且所有数据回滚。

嵌套是用来保持整个数据库的完整性的重要功能，但有时可能需要禁用嵌套，如果禁用了嵌套，那么修改一个触发器的实现不会再触发该表上的任何触发器。在下述情况下，需要禁用嵌套触发器：

嵌套触发要求复杂而有理论的设计，级联修改可能会修改用户不想涉及的数据。

在一系列嵌套触发器中的任意点的时间修改操作都会触发一些触发器，尽管这时数据库提供很强的保护功能，但如果以特定的顺序更新表，就会产生问题。

（4）递归触发器

        　 触发器的递归是指一个触发器从其内部再一次激活该触发器，例如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：数据库上点右键->属性->选项。 

2、管理触发器

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;

 

4.删除触发器

 

 --语法格式:

 

      drop  trigger   { trigger } [ ,...n ]

 

参数:

 

 trigger: 要删除的触发器名称

 

 n:表示可以删除多个触发器的占位符