SqlServer基础之(触发器)

?

五:创建触发器

?创建触发器的语法：?

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_inserton?student
after?insertasbegin
????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_age)values('貂蝉','女',30);????????????
select?stuCount?学生总人数?from?student_sum;insert?into?student(stu_name,stu_age)values('曹阿瞒',40);????????????????
select?stuCount?学生总人数?from?student_sum;

执行上面的语句后，结果如下图所示：

?既然定义了学生总数表student_sum表是向student表中插入数据后才计算学生总数的，所以学生总数表应该禁止用户向其中插入数据

--创建insert_forbidden,禁止用户向student_sum表中插入数据create?trigger?insert_forbiddenon?student_sum
after?insertasbegin
????RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror?是用于抛出一个错误rollback?transactionend

--触发触发器insert_forbiddeninsert?student_sum?(stuCount)?values(5);

结果如下：

?2.创建delete触发器

　　用户执行delete操作，就会激活delete触发器，从而控制用户能够从数据库中删除数据记录，触发delete触发器后，用户删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。

--创建delete触发器create?trigger?trig_deleteon?student?
after?deleteasbegin
????select?stu_id?as?已删除的学生编号,stu_name?stu_gender,stu_age????from?deletedend;

--执行一一条delete语句触发trig_delete触发器delete?from?student?where?stu_id=1;

?3.创建UPDATE触发器

　　update触发器是当用户在指定表上执行update语句时被调用被调用，这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作：更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。

--创建update触发器create?trigger?trig_updateon?student
after?updateasbegin
????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?insertedend

--创建完成，执行一条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_insteadOfon?student?
instead?of?insertas?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->T3...这样一直触发下去，最多允许嵌套?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).查看数据库中所有的触发器

???sysobjects??xtype

sysobjects?保存着数据库的对象，其中?xtype?为?TR?的记录即为触发器对象。在?name?一列，我们可以看到触发器名称。

(2).sp_helptext?查看触发器内容

use?数据库名goexec?sp_helptext?'触发器名称'

?将会以表的样式显示触发器内容。?

?除了触发器外，sp_helptext?还可以显示?规则、默认值、未加密的存储过程、用户定义函数、视图的文本。

(3).sp_helptrigger?用于查看触发器的属性

　　sp_helptrigger?有两个参数：第一个参数为表名；第二个为触发器类型，为?char(6)?类型，可以是?INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

use?数据库名goexec?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:表示可以删除多个触发器的占位符

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!