第十章 触发器

（5）删除触发器
【例10-10】删除触发器 trigger_tblDepartment
Use SouthSea Go drop trigger trigger_tblDepartment
（6）禁止或启用触发器
禁止或启用触发器的语法格式为：
Alter table 表名 {ENABLE|DISABLE} trigger {ALL|trigger_name[,…n]}
【例10-11】为SouthSea数据库创建一个触发器 SouthSea_safety，用于禁止用户删除表或更新表。
Use SouthSea Go If exists(select * from sys.triggers where parent_class=0 and name='SouthSea_safety') Drop trigger SouthSea_safety on database Go Create trigger SouthSea_safety On database For drop_table,alter_table As Begin Print '在删除表或修改表结构之前，请先禁止触发器SouthSea_safety！ ' rollback tran end
（2）验证触发器
【例10-5】验证触发器 trigger_tblAttendance，向表tblAttendance 中插入一条记录。
Use SouthSea Go insert into tblAttendance (EmployeeID,BreachTime,RecorderID,Type,Date) values ('emp99','2008/6/6','emp1','正常','2008/6/6')
当触发器触发时会去判断这个员工编号是否存在，若不存在则给 出提示信息，并回滚Insert操作。当然，如果插入一条存在的员工编号时， 如执行这条语句
【例10-6】验证触发器trigger_tblDepartment， 删除表tblDepartment中部门编号为'dep9'的部门。
Use SouthSea Go insert into tblDepartment values('dep9','市场部',null) go Insert into tblEmployee values('emp9','李小小','女','1982-819','lixx',0x82819,'lixx@sohu.com','dep9','69856344','2004-76',default,null) go alter table tblEmployee drop constraint fk_DeptID go delete from tblDepartment where deptID='dep9' 在删除了'dep9'号部门后，在tblEmployee表中相关的记录都被级联更新了。
2. 触发器概述
触发器是一种实施复杂数据完整性的特殊存储 过程，当对表或视图进行诸如UPDATE、 INSERT、DELETE这些操作时自动触发执行，以 防止对数据进行不正确、未授权或不一致的修改。 触发器与数据库中的表紧密相关，比如当对表执 行INSERT、UPDATE或DELETE操作时，触发器 就会自动执行。 触发器的主要作用就是其能够实现由主键和外键 所不能保证的复杂的参照完整性和数据的一致性。
6.DML触发器操作
（1）创建触发器 （2）验证触发器 （3）查看触发器 （4）修改触发器 （5）删除触发器
（1）创建触发器
注意事项
一个触发器只能对应一个表； 命名必须符合命名规则； CREATE TRIGGER 语句必须是批处理的第一个语句； 表的所有者具有创建触发器的缺省权限，表的所有者不能 把该权限传给其它用户； 触发器的SQL 语句可以参照其它数据库中的对象，但是， 触发器只能创建在当前数据库中； 触发器可以参照视图或临时表，但不能在视图或临时表上 创建触发器，而只能在基表或在创建视图的表上创建触发 器；
5. 触发器的原理
每个触发器被激活时，都会建立两 个临时表inserted 和 deleted:
执行insert时，被插入的语句会被放在 inserted临时表中 执行delete时，被删除的记录会被放在 deleted临时表中 执行update时，修改被分解为两部：
1. 删除原有的（deleted） 2. 添加修改后的（inserted）
Insert触发器
【例10-1】向tblAttendence表中插入一条考勤记 录时，先判断该员工编号是否存在，存在则插入， 不存在则给出提示信息：“该员工不存在，不能 有考勤信息！”
Use SouthSea Go CREATE TRIGGER trigger_tblAttendance ON tblAttendance For Insert AS if (select EmployeeID from inserted) not in (select EmployeeID from tblEmployee) begin raiserror('该员工不存在，不能有考勤信息！',16,1) rollback transaction end
3. 触发器的类型
SQL Server中的触发器可以分为两类： DML触发器和DDL触发器。
4. 触发器的触发方式
SQL Server支持两种类型的触发方式：
AFTER触发器：只能在表上定义该类触发器， 并且只有执行某一操作（INSERT、UPDATE、 DELETE）之后，触发器才被触发。 INSTEAD OF触发器：该类触发器并不执行 INSERT、UPDATE、DELETE所定义的操作， 而仅是执行触发器本身。
delete触发器
【例10-2】删除tblDepartment表中某条部门记录 时，先判断tblEmployee表中是否有员工属于该部 门，若有，则将这些员工所属的部门替换成“新 部门”。
Use SouthSea Go CREATE TRIGGER trigger_tblDepartment ON tblDepartment after delete as if exists(select * from deleted,tblEmployee where deleted.DeptID=tblEmployee.DeptID) begin update tblEmployee set DeptID='新部门' where DeptID in (select DeptID from deleted) end
执行该语句的结果是：该审核人不是这个员工的经理，不能更新！
（3）查看触发器
系统存储过程： （1） sp_help 通过sp_help ，可以了解触发器的一般信息，如触发 器的名字、属性、类型、创建时间。其命令格式是： sp_help ‘触发器名’ （2） sp_helptext 通过sp_helptext 能够查看触发器的正文信息。其语法格 式为： sp_helptext ‘触发器名’ （3） sp_depends 通过sp_depends 能够查看指定触发器所引用的表或指 定的表涉及到的所有触发器，其语法形式为： sp_depends ‘触发器名字’
7.DDL触发器操作
（1）创建DDL触发器 （2）验证DDL触发器
（1）创建DDL触发器
创建DDL触发器的语法结构如下：
CREATE TRIGGER trigger_name ON {ALL SERVER|DATABASE} [WITH ENCRYPTION] { {{FOR|AFTER}{event_type|event_group}[,…n] AS Sql_statement […n] }
（4）修改触发器
【例10-9】修改由【例10-1】创建的触发器 trigger_tblAttendance，使得表tblAttendance中不 允许插入记录。
ALTER TRIGGER trigger_tblAttendance ON tblAttendance Instead of Insert AS raiserror('表tblAttendance中不允许插入记录！',16,1)
【例10-7】验证触发器trigger_tblEmployee，将 员工编号为'emp6'的员工所在部门调换到'dep66' 号部门。
Use SouthSea Go update tblEmployee set DeptID='dep66' where EmployeeID='emp6'
该语句的执行结果是：返回消息“该部门不存在，禁止更新该 员工的部门信息！”和两个结果集，即临时表deleted和inserted的内容
update触发器
【例10-3】更新tblEmployee表中某位员工的记录时，先 判断更新后的部门编号是否存在，若不存在，则给出提示 信息：该部门不存在，禁止更新该员工的部门信息。
Use SouthSea Go CREATE TRIGGER trigger_tblEmployee ON tblEmployee for update AS IF (SELECT COUNT(*) FROM tblDepartment, Inserted where tblDepartment.DeptID=inserted.DeptID)=0 BEGIN select EmployeeID,Name,DeptID from deleted /*查看删除表中的内容*/ select EmployeeID,Name,DeptID from inserted /*查看插入表中的内容*/ raiserror('该部门不存在，禁止更新该员工的部门信息！',16,1) rollback transaction END
【例10-8】查看表tblLeave的记录，再将请假申 请编号为4的记录的审核人编号换为'emp4'。
Use SouthSea Go select * from tblLeave select * from tblDepartment select * from tblEmployee go update tblLeave set ApproverID='emp4' where LeaveID=4
第十章 触发器
内容：
1. 案例分析 2. 触发器概述 3. 触发器的类型 4. 触发器的触发方式 5. 触发器的原理 6.DML触发器操作 7.DDL触发器操作 8.习题
1Leabharlann Baidu 案例分析
现在要删除员工信息表中某个员工的信息、 或者向请假申请表中添加一条请假申请。 要考虑这样的一些情况：要删除的某位员 工在工资表、考勤表、请假申请表中都有 他的信息，那么我们做删除的时候应该要 级联删除这些对应的记录；想要添加的一 条请假申请的员工编号如果不存在，那么 应该拒绝添加等。
【例10-4】当更新tblLeave表中某条请假记录的 ApproverID（审核人编号）时，判断该审核人是否是该员 工所在部门的部门经理，如果不是，则禁止修改。
Use SouthSea Go CREATE TRIGGER trigger_tblLeave ON tblLeave for update AS if update(ApproverID) begin if (select ApproverId from inserted) not in (select managerId from tblDepartment,tblEmployee where (tblemployee.deptID= tblDepartment.deptID) and (employeeID=(select employeeID from deleted))) begin print '该审核人不是这个员工的经理，不能更新！' rollback transaction end end