1、第十二章,数据库触发器,课程内容回顾,存储过程是一组预编译的SQL语句，可以包含数据操纵语句、逻辑控制语句或数据检索语句。数据库开发人员或管理员可以通过编写存储过程来运行经常执行的任务、或者应用复杂的业务规则。存储过程可提高应用程序访问数据的速度，帮助实现模块化编程，提高数据库性能和数据安全性存储过程可分为：系统存储过程用户定义的存储过程CREATE PROCEDURE 语句用于创建用户定义的存储过程。EXECUTE 语句用于运行存储过程。存储过程的参数分为输入参数和输出参数，输入参数用来向存储过程传入值，输出参数从存储过程中返回（输出）值。RAISERROR语句用来向用户报告错误,本章目标,
2、了解触发器的用途理解触发器的工作原理掌握如何使用inserted表和deleted表掌握如何创建INSERT、UPDATE、DELETE触发器,触发器介绍,触发器是在数据库中发生事件时自动执行的特殊存储过程，这些事件主要是发生在表上的DML（INSERT,UPDATE,DELETE）操作触发器与数据操作有关 在数据库服务器端实现业务规则和强制业务规则存储过程实现业务规则 约束强制业务规则 强制业务规则约束触发器触发器可以实现比约束更复杂的数据完整性,触发器的特点,触发器是在对表进行插入、更新或删除操作时自动执行的存储过程 触发器通过事件进行触发而被执行的 特点:与表相关联 自动触发 不能直接调
3、用是事务的一部分,触发器的种类,AFTER触发器AFTER触发器要求只有执行某一操作INSERT、UPDATE、DELETE之后触发器才被触发且只能在表上定义 INSTEAD OF触发器 可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器,AFTER触发器,AFTER触发器包括:INSERT触发器：当向表中插入数据时触发，自动执行触发器所定义的SQL语句。UPDATE触发器：当更新表中某列、多列时触发，自动执行触发器所定义的SQL语句。DELETE触发器：当删除表中记录时触发，自动执行触发器所定义的SQL语句。触发器的两个特殊表插入表（inserted表）
4、删除表（deleted表): 这两个表是逻辑表，并且是由系统管理的，存储在内存中，不是存储在数据库中，因此，不允许用户直接对其修改。,inserted 和deleted 表,触发器触发时：系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作deleted 表临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入
5、操作,inserted 和deleted 表,inserted表和deleted表存放的信息,创建触发器,语法:,CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE, INSERT, UPDATE AS T-SQL语句GO,WITH ENCRYPTION表示加密触发器定义的SQL文本DELETE, INSERT, UPDATE指定触发器的类型,触发器示例,问题:在emp表上创建一个触发器，该触发器保证每次最多只能删除一个雇员,USE empDBGO/*-创建触发器-*/CREATE TRIGGER emp_d
6、elete ON empFOR DELETEAS IF (SELECT COUNT(*) FROM Deleted)1BEGIN RAISERROR(不能删除多于一个雇员，16，1) ROLLBACK TRANSACTION END,测试:删除多于一条记录,看结果如何?,禁用或启用触发器,用户可以禁用、启用一个指定的触发器或一个表的所有触发器 语法示例:禁用触发器emp_delete示例:启用触发器emp_delete,ALTER TABLE emp ENABLE | DISABLE TRIGGER ALL | trigger_name，n,ALTER TABLE emp DISABLE TR
7、IGGER emp_delete,ALTER TABLE emp ENABLE TRIGGER emp_delete,更改和删除触发器,修改触发器语法:删除触发器语法:示例:删除触发器emp_delete,ALTER TRIGGER Trigger_nameON table_name WITH ENCRYPTION FOR DELETE,INSERT,UPDATEAS SQL 语句,DROP TRIGGER trigger_name,DROP TRIGGER emp_delete,INSERT触发器,INSERT触发器的工作原理：,插入记录行,触发insert触发器。向inserted表中插入
8、新行的副本,触发器检查inserted表中插入的新行数据，确定是否需要回滚或执行其他操作,INSERT 触发器示例-1,问题:当在员工表(emp)中输入数据时，确保输入的员工工资不超过5000元人民币 分析:在emp表上创建一个INSERT触发器CheckSal从inserted临时表中获取插入的数据行判断工资的值是否超过5000,INSERT 触发器示例-1,-主要代码-CREATE TRIGGER CheckSalON emp FOR INSERTAS DECLARE sal money SELECT sal=sal FROM inserted IF sal 5000 BEGIN PRIN
9、T 工资不能超过5000 PRINT 请将工资修改为小于5000的值 ROLLBACK TRANSACTION END GO,DELETE触发器,DELETE触发器的工作原理：,删除记录行,触发delete触发器向deleted表中插入被删除的副本,触发器检查deleted表中被删除的数据，决定是否需要回滚或执行其他操作,DELETE 触发器示例-1,问题:当删除雇员时，自动备份被删除的数据到表backupTable中 分析:应在雇员表上创建DELETE触发器被删除的数据可以从deleted表中获取,DELETE 触发器示例-1,USE empDBGO/*-检测是否存在，触发器存放在系统表sy
10、sobjects中-*/IF EXISTS (SELECT name FROM sysobjects WHERE name=trig_delete_emp) DROP TRIGGER trig_delete_empGO/*-创建DELETE触发器：在emp上创建删除触发器-*/CREATE TRIGGER trig_delete_empON empFOR DELETEAS print 开始数据备份，请稍侯. IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=backupTable) SELECT * INTO backupTable FROM
11、deleted -从deleted表中获取被删除的数据 ELSE INSERT INTO backupTable SELECT * FROM deleted print 备份数据成功，备份表中的数据为： SELECT * FROM backupTableGO/*-测试触发器：删除数据-*/SET NOCOUNT ON -不显示T-SQL语句影响的记录行数DELETE FROM emp-查看结果print 雇员表中的数据：SELECT * FROM emp,UPDATE触发器,UPDATE触发器的工作原理,向deleted表中插入被删除的副本,检查deleted和inserted表中的数据，确定
12、是否需要回滚或执行其他操作,向inserted表中插入被添加的副本,插入记录行,UPDATE触发器示例-1,问题:检测员工加薪比例，如果加薪额度超过其基本工资的20%，则停止加薪，并给出错误提示。 分析:在员工表上创建UPDATE触发器， 删除更改前原有的数据行：删除的数据转移到了deleted表中。再插入更改后的新行：插入的数据同时也保存在inserted表中。,UPDATE触发器示例-1,-关键代码-CREATE TRIGGER trig_update_empON empFOR UPDATEAS DECLARE beforeSal Money,afterSal Money -定义变量 SE
13、LECT 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 -回滚事务，撤消加薪
14、ENDGO,列级 UPDATE 触发器,UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据 使用UPDATE（列）函数检测是否修改了某列,问题：在雇员表(emp)中,雇员号作为主键,一般不允许修改。,分析：UPDATE（列名）函数可以检测是否修改了某列,列级 UPDATE 触发器示例,USE empDBGO/*-创建UPDATE触发器：在雇员表emp上创建更新（列）触发器 -*/CREATE TRIGGER emp_update ON emp FOR UPDATEAS IF UPDATE(empno)BEGIN print 修改失败. RAISERROR(*不允许修
15、改雇员号！,16,1) ROLLBACK TRANSACTION -回滚事务，撤消操作ENDGO/*-测试触发器：修改雇员编号*/-SET NOCOUNT ONUPDATE EMP SET EMPNO=111 WHERE EMPNO=101,基于视图的INSTEAD OF触发器,可以在表或视图上定义INSTEAD OF 触发器，执行这种触发器就能够替代原始的触发动作 INSTEAD OF 触发器被用于更新那些没有办法通过正常方式更新的视图 INSTEAD OF 触发器包含代替原始数据操作语句的代码。,USE empDBGO/*-创建基于两个表emp和dept联接的视图-*/CREATE VIE
16、W emp_deptAS SELECT empno,ename,sal,dname FROM emp e, dept dWHERE e.deptno=d.deptno/*-查看视图数据-*/SELECT * FROM emp_dept/*-测试：试图从视图中删除数据-*/DELETE FROM emp_dept WHERE empno=101,基于视图的INSTEAD OF触发器,要使得能够对emp_dept视图进行更新，必须通过INSTEAD OF 触发器来删除数据 示例:：通过视图删除数据,/*-创建INSTEAD OF触发器-*/ CREATE TRIGGER del_emp ON em
17、p_dept INSTEAD OF DELETE AS DELETE emp WHERE empno IN (SELECT empno FROM DELETED)/*-测试：使用视图删除数据 -*/SET NOCOUNT ONDELETE FROM emp_dept WHERE empno=101,使用触发器的考虑事项,大多数触发器是后反应的，而约束和INSTEAD OF触发器是前反应的首先检查约束表可以拥有多个任意动作的触发 触发器可以处理多行动作,总结,触发器是在对表进行插入、更新和删除操作时自动执行的存储过程。触发器通常用于强制业务规则。触发器是一个特殊的事务单元，当出现错误时，可以执行ROLLBACK TRANSACTION回滚撤消操作触发器可以使用临时表：deleted表和inserted表，它们动态驻留在内存中的，当触发器工作完成，它们也被删除；它们存放了被删除或插入记录的行副本。触发器从触发的动作来分，包括：INSERT触发器：当试图向表中插入数据时，将执行INSERT触发器。这种触发器可以确保插入到表中的数据是有效的。UPDATE触发器：当对表执行更新操作时，将执行UPDATE触发器。DELETE触发器：当从表中删除数据时，将执行DELETE触发器。INSTEAD OF 触发器包含代替原始数据操作语句的代码。,
