第10章触发器.ppt

1、第10章、触发器,10.1 触发器概述,触发器是一种特殊类型的存储过程，由一组SQL语句组成;它有助于更好地维护数据库的完整性。 创建约束、规则和默认值的数据完整性维护方法只能进行简单操作，有一定的局限性。 触发器是依存于表的数据库对象,在表执行修改操作(插入/更新/删除)时自动执行,即是通过事件进行触发而被执行的. 而存储过程可以通过存储过程名称而被直接调用(execute)。,上页,下页,结束,触发器功能：,(1)级联修改数据库中相关的表。 如员工表中出于某种需要删除某员工，项目表中与之相关的记录应作调整：或修改、或删除。 (2)执行比检查约束更为复杂的约束操作。 书写更为复杂的T-SQL

2、语句（引用多个表，使用IF- ELSE等语句作更为复杂的检查）。 (3)拒绝或回滚违反引用完整性的操作。 检查对数据表的操作，是否违反引用完整性，并选择相应的操作。 (4)比较表修改前后数据之间的差别，并根据差别采取相应的操作。 如对员工表规定每次工资变动幅度不能超过40%,使用触发器可以将修改前后的数据进行比较，若超过40%的限制，可回滚该修改操作。,触发器类型：,可以分为：AFTER触发器和INSTEAD OF触发器两种。 AFTER触发器： 这种触发器将在数据变动(insert,update,delete操作)完成以后才触发。对变动的数据进行检查，如果发现错误，则拒绝或回滚变动的数据。

3、INSTEAD OF触发器： 这种触发器将在数据变动以前被触发。并取代变动数据 的操作(insert,update,delete操作)，转而去执行触发器定义的操作。 在建立触发器时，还必须指定触发操作： insert、update、delete操作，至少指定一种，也可指定多种。,触发器结构：,上页,下页,结束,事件 UPDATE INSERT DELETE,条件 AFTER INSTEAD OFF,动作 各种 Transact-SQL 语句,有时，触发器也称主动规则，或称事件条件动作规则(ECA),上页,下页,结束,触发事件发生,After 条件触发器执行示意图,执行触发事件,执行触发器动作,

4、触发事件发生,Instead of 条件触发器执行示意图,不执行触发事件,执行触发器动作,一、 创建触发器的准备,在同一个数据表中可以创建多个after触发器，在表或视图上，每个insert、update、delete语句最多可以定义一个instead of触发器。 创建触发器应该考虑以下几个问题： CREATE TRIGGER 语句必须是批处理中的第一个语句。 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。 触发器为数据库对象，其名称遵循标识符的命名规则。 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。,10.2 创建触发器,虽然不能在临时表或系

5、统表上创建触发器，但是触发器可以引用临时表。 为某数据表创建外键约束时，选中级联删除相关字段，不能定义INSTEAD OF DELETE 触发器；选中级联更新相关字段，不能定义INSTEAD OF UPDATE 触发器。 虽然TRUNCATE TABLE语句类似于没有WHERE子句（用于删除行）的DELETE 语句，但它并不会引发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。 当创建一个触发器时必须指定： 名称； 在其上定义触发器的表； 触发器将何时激发； 激活触发器的数据修改语句。,1. 使用企业管理器创建触发器,步骤： 展开指定的服务器和数据库项； 展开要在其上

6、创建触发器的表所在的数据库； 右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项； 出现触发器属性对话框，在名称文本框中选择新建； 在文本框中输入触发器文本。 单击“检查语法”按钮，检查语法是否正确； 单击“应用”按钮，在名称下拉列表中有新创建的触发器名称。 单击“确定”按钮，即关闭该对话框，成功创建触发器。,2. 使用CREATE TRIGGER命令创建触发器,其语法形式如下： CREATE TRIGGER trigger_name ONtable|view WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE, INSERT ,

7、UPDATE WITH APPENDNOT FOR REPLICATIONAS,IF UPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.n sql_statement.n ,示例,SQL Server不支持在触发器中包含所有的create语句、DROP语句等,例：创建触发器trigger_students，实现当修改students表中的数据时，显示提示信息“学生基本情况被修改了”。,定义文本： CR

8、EATE TRIGGER trigger_students ON students after UPDATE AS PRINT 学生基本情况被修改了 GO,例.创建触发器示例 use testdb go -创建触发器t_i create trigger t_i on 员工表 after insert as print 欢迎新员工加入本公司! go -创建触发器t_d create trigger t_d on 员工表 after delete as print 有员工离开本公司! go,SET NOCOUNT ON -试验触发器的操作 INSERT INTO 员工表(姓名,性别,所属部门,工资

9、) values (周小涛,男,办公室,2600) DELETE FROM 员工表 WHERE 编号10 go,二、 查看、修改和删除触发器,1. 查看触发器 （）使用企业管理器查看触发器信息 （）使用系统存储过程查看触发器,上页,下页,结束,sp_help sp_helptext sp_depends,sp_help： 用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。 sp_help 触发器名称 sp_helptext： 用于查看触发器的正文信息 sp_helptext 触发器名称 sp_depends： 用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。 sp_d

10、epends 触发器名称 sp_depends 表名,上页,下页,结束,2. 修改触发器,（）使用企业管理器修改触发器正文 在企业管理器中，展开指定的服务器和数据库，选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。,上页,下页,结束,（）使用sp_rename命令修改触发器的名称,sp_rename命令的语法形式如下： sp_rename oldname,newname,上页,下页,结束,（）使用

11、alter trigger命令修改触发器正文,语法形式如下： ALTER TRIGGER trigger_name ON(table|view) WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOT FOR REPLICATION AS sql_statement.n|,上页,下页,结束,(FOR|AFTER|INSTEADOF)INSERT,UPDATENOT FOR REPLICATIONASIF UPDATE(column)AND|ORUPDATE(column).n| IF ( COLUMNS

12、_UPDATED ( ) bitwise_operator updated_bitmask )comparison_operatorcolumn_bitmask.n sql_statement.n ,上页,下页,结束,3. 删除触发器,（）使用系统命令DROP TRIGGER删除指定的触发器，其语法形式如下： DROP TRIGGER trigger ,.n （）删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。 （）在企业管理器中，用右键单击要删除的触发器所在的表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在名称选项框中选择要删

13、除的触发器，单击“删除”按钮，即可删除该触发器。,上页,下页,结束,10.3 触发器的应用,本节所涉及的数据表是：,上页,下页,结束,编号 int IDENTITY (1, 1) NOT NULL, 姓名 char (10)，性别 char (2), 所属部门 varchar (50)，工资 money,客户编号 int IDENTITY (1, 1) NOT NULL , 客户名称 varchar (30), 地址 varchar (80) 联系人 char (10), 电话 char (12),项目编号 int IDENTITY (1, 1) NOT NULL , 名称 varchar (

14、50),负责人 int , 客户 int NULL ,开始日期 datetime , 结束日期 datetime,员工表,客户表,项目表,1. 创建AFTER INSERT触发器 例：在员工表中创建一个触发器，将新员工工资限制在不超过5000以内，若插入的员工工资超过5000，则拒绝插入该记录。,上页,下页,结束,Create trigger t_ gz on 员工表 after insert as if (select 工资 from inserted)5000 begin print 新员工工资不能超过5000! rollback end go set nocount on insert

15、into 员工表 (姓名,性别,所属部门,工资) values (谢军,女,办公室,6000),2. 创建AFTER UPDATE触发器 修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录。 例:在员工表中创建触发器,限制每次工资额的变动不能超过2000.,例：在student表中创建两个触发器，报告学生的增加和删除。,create trigger trigger_come on students after insert as Print 欢迎新生来到本校 go create trigger trigger_leave on students

16、after delete as Print 祝贺你顺利毕业,CREATE TRIGGER t_gz_change ON 员工表 AFTER UPDATE AS IF UPDATE(工资) -update(字段名)函数用于返回字段是否修改(逻辑值真与假) BEGIN IF (SELECT MAX(ABS(inserted.工资-deleted.工资) FROM inserted JOIN deleted ON inserted.编号=deleted.编号)2000 BEGIN PRINT 工资额的变动不能超过2000! ROLLBACK TRANSACTION END,END ELSE PRIN

17、T 工资无变动! 再执行如下语句: update 员工表 set 工资=8000 where 编号=1 又执行如下语句: update 员工表 set 姓名=张明权 where 编号=2 请比较反馈消息!,3. 创建AFTER DELETE触发器,考虑的情况是: 为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。 例:在客户表中创建一个触发器,监控删除的客户记录, 若删除的记录在项目表中有合作项目,且项目尚未完工,则不可删除,否则可以删除.,CREATE TRIGGER t_del ON 客户表 AFTER DELETE AS IF EXISTS(SELECT * FROM 项目表

18、JOIN deleted ON 项目表.客户= deleted.客户编号 WHERE 项目表.结束日期=getdate() ) BEGIN PRINT 本客户项目尚未结束!不能删除! ROLLBACK TRANSACTION END 提示:请注意先将客户表与项目表的外键约束删除再执行后面的相关删除语句 DELETE 客户表 WHERE 客户编号=11,4. 创建INSTEAD OF触发器,INSTEAD OF触发器可用来取代激活触发器的insert,update,delete操作,转而由触发器中的程序控制数据的修改. 当INSTEAD OF触发器被激活时,inserted,deleted临时表

19、都已存入数据.这与 AFTER触发器相同;差别在于AFTER触发器激活前,表中数据已修改,而INSTEAD OF触发器被激活时还未实际修改表中数据. INSTEAD OF触发器的另一个优点是:可根据具体插入情况避免整个批的中断. 例,上页,下页,结束,CREATE TRIGGER t_in ON 员工表 INSTEAD OF INSERT AS SET NOCOUNT ON UPDATE 员工表 SET 员工表.姓名=inserted.姓名, 员工表.性别=inserted.性别, 员工表.所属部门=inserted.所属部门, 员工表.工资=inserted.工资 FROM 员工表 JOIN

20、 inserted ON 员工表. 姓名=inserted. 姓名 PRINT 在原来基础上修改了+CAST(ROWCOUNT AS VARCHAR) +条记录,INSERT 员工表 SELECT 姓名,性别,所属部门,工资 FROM inserted WHERE inserted.姓名 NOT IN (SELECT 姓名 FROM 员工表) PRINT 插入了+CAST(ROWCOUNT AS VARCHAR) +条记录 GO,例：假定有数据表employee,包含字段姓、名、工资。基于该表创建了一个视图emp_view，包含字段姓名、工资两个字段，其中字段姓名 由基表的字段姓和名组合而来 ； CREATE VIEW emp_view(姓名, 工资) AS SELECT 姓+名 AS 姓名, 工资 FROM employee 现在，不能通过视图emp_view向数据表employee添