第12章触发器

存储过程实现业务规则 约束强制业务规则
强制业务规则
约束 触发器
触发器可以实现比约束更复杂的数据完整性
3
触发器的特点
触发器是在对表进行插入、更新或删除操作时自动执行的 存储过程 触发器通过事件进行触发而被执行的 特点:
与表相关联 自动触发 不能直接调用 是事务的一部分
ename sal
003
inserted
empno
002
JAK
3000
触发器检查inserted表中插入的新行数据， 确定是否需要回滚或执行其他操作 13
INSERT 触发器示例-1
问题: 当在员工表(emp)中输入数据时，确保输入的员工工资不超 过5000元人民币
分析: • 在emp表上创建一个INSERT触发器CheckSal • 从inserted临时表中获取插入的数据行 • 判断工资的值是否超过5000
第十二章
数据库触发器
课程内容回顾
存储过程是一组预编译的SQL语句，可以包含数据操纵语句、逻辑控 制语句或数据检索语句。 数据库开发人员或管理员可以通过编写存储过程来运行经常执行的任 务、或者应用复杂的业务规则。 存储过程可提高应用程序访问数据的速度，帮助实现模块化编程，提 高数据库性能和数据安全性 存储过程可分为：
… GO
15
DELETE触发器
DELETE触发器的工作原理：
emp
删除记录行
empno
Ename
sal
001
002
JONE
jAK smiths
2000
3000 1500
003
deleted
empno ename
触发delete触发器 向deleted表中插入 被删除的副本
sal
002
JAK
3000
系统存储过程 用户定义的存储过程
CREATE PROCEDURE 语句用于创建用户定义的存储过程。 EXECUTE 语句用于运行存储过程。 存储过程的参数分为输入参数和输出参数，输入参数用来向存储过程 传入值，输出参数从存储过程中返回（输出）值。 RAISERROR语句用来向用户报告错误
ALTER TABLE emp DISABLE TRIGGER emp_delete
示例:启用触发器emp_delete
ALTER TABLE emp ENABLE TRIGGER emp_delete
11
更改和删除触发器
修改触发器 语法:
ALTER TRIGGER Trigger_name ON table_name [WITH ENCRYPTION] FOR {[DELETE,INSERT,UPDATE]} AS SQL 语句
20
UPDATE触发器示例-1
--关键代码-CREATE TRIGGER trig_update_emp ON emp FOR UPDATE AS DECLARE @beforeSal Money,@afterSal Money --定义变量 SELECT @beforeSal=sal FROM deleted --获取加薪前的工资 SELECT @afterSal=sal FROM inserted --获取加薪后的工资 select @afterSal,@beforeSal IF ABS(@afterSal-@beforeSal)>=@beforeSal*0.2 --判断加薪额度是否超过% BEGIN print '加薪额度：' +convert(varchar(8),ABS(@afterSal-@beforeSal)) RAISERROR ('加薪额度不能超过其工资的百分之二十,加薪失败',16,1) ROLLBACK TRANSACTION --回滚事务，撤消加薪 END GO
DELETE, INSERT, UPDATE指定触发器的类型
9
触发器示例
问题:在emp表上创建一个触发器，该触发器保证每次最多只能删除一个 雇员
USE empDB GO /*--创建触发器--*/ CREATE TRIGGER emp_delete ON emp FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted)>1 BEGIN RAISERROR(‘不能删除多于一个雇员’，16，1) ROLLBACK TRANSACTION END
INSERT触发器：当向表中插入数据时触发，自动执行触发器所 定义的SQL语句。 UPDATE触发器：当更新表中某列、多列时触发，自动执行触发 器所定义的SQL语句。 DELETE触发器：当删除表中记录时触发，自动执行触发器所定 义的SQL语句。
触发器的两个特殊表
插入表（inserted表） 删除表（deleted表): 这两个表是逻辑表，并且是由系统管理的，存储在内存中，不是存储 在数据库中，因此，不允许用户直接对其修改。
inserted表和deleted表存放的信息
8
创建触发器
语法:
CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR [DELETE, INSERT, UPDATE] AS T-SQL语句 GO
WITH ENCRYPTION表示加密触发器定义的SQL文本
21
列级 UPDATE 触发器
UPDATE触发器除了跟踪数据的变化（修改）外，还可以 检查是否修改了某列的数据 使用UPDATE（列）函数检测是否修改了某列 问题：
在雇员表(emp)中,雇员号作为主键,一般不允许修改。
分析：
UPDATE（列名）函数可以检测是否修改了某列
22
列级 UPDATE 触发器示例
USE empDB GO /*--创建UPDATE触发器：在雇员表emp上创建更新（列）触发器 --*/ CREATE TRIGGER emp_update ON emp FOR UPDATE AS IF UPDATE(empno) BEGIN print '修改失败....' RAISERROR('****不允许修改雇员号！',16,1) ROLLBACK TRANSACTION --回滚事务，撤消操作 END GO /*--测试触发器：修改雇员编号*/ --SET NOCOUNT ON UPDATE EMP SET EMPNO=‘111’ WHERE EMPNO='101'
4
触发器的种类
AFTER触发器
AFTER触发器要求只有执行某一操作INSERT、UPDATE、 DELETE之后触发器才被触发且只能在表上定义
INSTEAD OF触发器
可在表上定义INSTEAD OF 触发器，也可以在视图上定义 INSTEAD OF 触发器
5
AFTER触发器
AFTER触发器包括:
6
inserted 和deleted 表
触发器触发时：
系统自动在内存中创建deleted表或inserted表 只读，不允许修改；触发器执行完成后，自动删除
inserted 表
临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作
删除触发器 语法:
DROP TRIGGER trigger_name
示例:删除触发器emp_delete
DROP TRIGGER emp_delete 12
INSERT触发器
INSERT触发器的工作原理：
emp
插入记录行
empno
Ename
sal
001
002
JONE jAK smiths
2000 3000 1500 触发insert触发器。 向inserted表中插 入新行的副本
empno ename sal
002
JAK
3000
002
JAK
3500
检查deleted和inserted表中的数据， 确定是否需要回滚或执行其他操作
19
UPDATE触发器示例-1
问题:
检测员工加薪比例，如果加薪额度超过其基本工资的20%，则停止加薪， 并给出错误提示。
分析:
• 在员工表上创建UPDATE触发器， • 删除更改前原有的数据行：删除的数据转移到了deleted表中。 • 再插入更改后的新行：插入的数据同时也保存在inserted表中。
1
本章目标
了解触发器的用途 理解触发器的工作原理 掌握如何使用inserted表和deleted表 掌握如何创建INSERT、UPDATE、DELETE触发器
2
触发器介绍
触发器是在数据库中发生事件时自动执行的特殊存储过 程，这些事件主要是发生在表上的DML （INSERT,UPDATE,DELETE）操作 触发器与数据操作有关 在数据库服务器端实现业务规则和强制业务规则
测试:删除多于一条记录,看结果如何?
10
禁用或启用触发器
用户可以禁用、启用一个指定的触发器或一个表的所有触发器 语法
ALTER TABLE emp {ENABLE Baidu Nhomakorabea DISABLE } TRIGGER {ALL | trigger_name[，…n]}
示例:禁用触发器emp_delete
18
UPDATE触发器
UPDATE触发器的工作原理
emp
插入记录行
empno ename sal
001
Jone
2000
002
向deleted表中插入 被删除的副本
Deleted（更新前的数据）
empno ename sal
JAK
3500
向inserted表中插 入被添加的副本
Inserted（更新后的数据）
14
INSERT 触发器示例-1
----主要代码----CREATE TRIGGER CheckSal ON emp FOR INSERT AS DECLARE @sal money SELECT @sal=sal FROM inserted IF @sal > 5000 BEGIN PRINT '工资不能超过5000' PRINT '请将工资修改为小于5000的值' ROLLBACK TRANSACTION END
USE empDB GO /*---检测是否存在，触发器存放在系统表sysobjects中--------*/ IF EXISTS (SELECT name FROM sysobjects WHERE name=’trig_delete_emp’) DROP TRIGGER trig_delete_emp GO /*----创建DELETE触发器：在emp上创建删除触发器-----*/ CREATE TRIGGER trig_delete_emp ON emp FOR DELETE AS print '开始数据备份，请稍侯......' IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='backupTable') SELECT * INTO backupTable FROM deleted --从deleted表中获取被删除的数据 ELSE INSERT INTO backupTable SELECT * FROM deleted print '备份数据成功，备份表中的数据为：' SELECT * FROM backupTable GO /*--测试触发器：删除数据--*/ SET NOCOUNT ON --不显示T-SQL语句影响的记录行数 DELETE FROM emp --查看结果 print ‘雇员表中的数据：' SELECT * FROM emp
deleted 表
临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作
7
inserted 和deleted 表
修改操作 增加(INSERT)记录 删除(DELETE)记录 修改(UPDATE)记录 inserted表 存放新增的记录 ----存放更新后的记录 deleted表 -----存放被删除的记录 存放更新前的记录
触发器检查deleted表中被删除的数据，决 定是否需要回滚或执行其他操作 16
DELETE 触发器示例-1
问题: 当删除雇员时，自动备份被删除的数据到表backupTable中 分析: • 应在雇员表上创建DELETE触发器 • 被删除的数据可以从deleted表中获取
17
DELETE 触发器示例-1