第十一章 触发器

1、第十章 触发器(Trigger)触发器是数据库服务器中发生事件时能自动执行的一种特种存储过程，主要用于强制规则和数据完整性。1、触发器概述11触发器的概念触发器是一种特殊类型的存储过程，主要通过事件进行触发而被执行的。由DML触发的是DML触发器，由DDL触发的是DDL触发器（DDL触发器是SQL Server 2005中新增的，分为数据库级的DDL触发器和服务器级的DDL触发器）。这里主要介绍DML触发器。DML触发器是和数据库中的表相关的，当对表进行INSERT、UPDATE、DELETE操作时，将触发相应的触发器。在SQL Server中，有两类触发器，一类是标准（After）触发器，另

2、一类是新的Instead-of触发器。12触发器的功能l 强化约束。触发器能够实现比CHECK更为复杂的约束。l 级联修改。当某张表中的数据发生变化时，通过触发器对与之关联的表进行相应的更新。l 实施数据完整性。使用触发器禁止或回滚违反数据完整性的操作或其它不经许可的操作。l 更新前后状态比较。触发器可以区对数据更新操作的前后状态差别，并可以完成基于这种差别的特定动作。13说明（DML触发器）l 触发原因。每个修改语句（Insert、Update、Delete）都会引发一次触发器的执行。甚至当修改语句影响了0条记录时也会被引发。l 触发时机。After触发器是在修改语句成功完成之后被引发，而I

3、nstead-of触发器是在修改语句完成之前（Inserted和Deleted表被建立之后），在所有约束之前被启动l 视图触发器。对视图只能建立Instead-of触发器，不能建立After触发器。l 一个表的一个操作能定义多个AFTER触发器，根据定义的先后顺序被触发，也可以通过sp_settriggerorder来改变触发顺序。一个表或一个视图对于每种操作只能定义一个Instead-of触发器。l2、触发器的创建在SQL Server中创建触发器有两种方法：使用企业管理器创建和使用CREATE TRIGGER语句创建。1 使用企业管理器创建创建触发器步骤（演示）2使用CREATE TRIG

4、GER语句创建触发器（一）创建触发器的简化语法CREATE TRIGGER trigger_nameON table_name | viewFOR | AFTER | INSTEAD OFINSERT,UPDATE,DELETEASsql_statementn（二）格式说明（1）trigger_name：触发器名称。每个 trigger_name 必须遵循标识符规则，但 trigger_name 不能以 # 或 # 开头。（2）table_name | view：对其执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。视图只能被 INSTEAD OF 触发器引用。不能对局部或全局临时

5、表定义 DML 触发器。（3）FOR | AFTER：指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被激发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。不能对视图定义 AFTER 触发器。一个操作能定义多个AFTER触发器，根据定义的先后顺序被触发。（4）INSTEAD OF：表示不执行其所定义的操作（INSERT、UPDATE、DELETE）而仅执行触发器本身。这种触发器可以在表和视图上定义。但对于同一操作只能定义一个INSTEAD OF触发器。INSTEAD OF触发器在所有约束之前被启动，其优先级高于触发语句的操作，这个行为和AFTER触发器

6、有很大不同。如果在INSTEAD OF触发器内部启动了修改语句（INSERT、UPDATE、DELETE），修改语句不会再次启动触发器。一个操作只能有一个INSTEAD OF触发器。（5）INSERT,UPDATE,DELETE：用来指明哪种数据操作将激活触发器，允许使用上述选项的任意顺序组合，顺序不受限制。（三）例例1：在employee表上创建一个delete触发器，显示被删除员工的个数信息CREATE TRIGGER tr_employee ON employeeAFTER DELETEASDECLARE msg varchar(50)SELECT msg=STR(ROWCOUNT)+

7、were deletedSELECT msgRETURN例2：在employee表上创建一个instead of 触发器，当插入一条记录时，先判断lastname上的值是否存在，如果不存在则插入，否则不插入。create trigger insert_employee on employeesinstead of insertasif not exists(select * from employees,inserted where inserted.lastname = employees.lastname)insert into employees(employeeid,lastname,

8、firstname) select employeeid,lastname,firstname from insertedset IDENTITY_INSERT dbo.employees oninsert into employees(employeeid,lastname,firstname)values(11,'bb','bbbb')3、inserted和deleted虚拟表在触发器的执行过程中，SQL Server要维护两个临时的虚拟表：inserted和deleted。Inserted表包含引发触发器操作过程中被插入的所有记录，deleted表包含引发

9、触发器操作过程中被删除的所有记录（update操作可理解为先删除，后插入）。我们可以利用这一特性来测试某些数据修改的效果，以及设置触发操作的条件。触发器被激活时，两个表的内容如下： Transact-SQL语句Inserted表Deleted表 INSERT新添加的行空（N/A） UPDATE新行旧行 DELETE空被删除的行例：为customers表创建一个名为test_tr的触发器，当执行添加、更新或删除时，激活该触发器。create trigger test_tron customersfor insert,update,deleteasselect * from insertedsel

10、ect * from deleted4、通过触发器处理多记录上的改动SQL语句是集合操作，Insert、Update、Delete也同样。有时需要通过触发器一条记录一条记录的处理更改（这里不包括Delete），函数update()和columns_updated()非常有用。update()函数：对于Update操作，确定在update操作过程中指定列是否已被更更，是则返回True，对于Delete操作，对所有列的测试均返回True。columns_update()函数：测试是否插入或更新了提及的列，仅用于 INSERT 或 UPDATE 触发器中。COLUMNS_UPDATED 返回 var

11、binary 位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED 函数以从左到右的顺序返回位，最左边的位表示表中的第一列（参见CREATE TRIGGER）。例：为products表建立一个名为muti_updated触发器，当修改单价大于100的产品的单价时，自动记录修改信息。create table updatedinfo(productid int primary key,oldunitprice money,newunitprice money,updatetime datetime)goCREATE TRIGGER muti_updated ON dbo.Products FOR UPDATEASif update(unitprice)insert into updatedinfo(productid,oldunitprice,newunitprice,updatetime) select ductid,d.unitprice,i.unitprice,getdate() from deleted d inner join inserted i on ductid=ductidgoupdate pr