第六章 触发器.ppt

第六章触发器 6 1概述6 2创建和使用DML触发器6 3创建和使用DDL触发器6 4管理触发器 6 1概述 触发器是一种特殊类型的存储过程它不能被显式地调用 而是当表中插入 更改或者删除记录时 触发器被自动触发执行用触发器可以实施更为复杂的数据完整性约束触发器是在特定表上进行定义的SQLServer2005包括两大类触发器 1 DML触发器在数据库中发生DML事件时将启用DML事件包括在指定表或视图中修改数据的INSERT语句 UPDATE语句或DELETE语句 2 DDL触发器是SQLServer2005的新增功能 当服务器或数据库中发生DDL事件时将调用这些触发器CREATE ALTER DROP 触发器和存储过程的区别存储过程是在数据库上定义的 而触发器是在特定表上进行定义的存储过程可以由用户直接调用执行 但触发器不能直接调用执行 而是SQLSERVER自动触发执行的存储过程根据需要可以带有输入输出参数 触发器不允许带参数 触发器的作用触发器可以对数据进行级联修改触发器可以完成比CHECK约束更为复杂的限制触发器可以发现改变前后表中数据的不同 并根据这些不同来进行相应的操作 6 2创建和使用DML触发器 当创建一个触发器时必须指定如下选项 1 名称 2 在其上定义触发器的表 3 触发器将何时激发 分为 FOR AFTER INSTEADOF触发器 4 激活触发器的数据修改语句有效选项为INSERT UPDATE或DELETE 5 执行触发操作的编程语句 例 CREATETRIGGERtrig stuONStudentsAFTERINSERT DELETE UPDATEASSELECT FROMstudents 创建DML触发器的语法 CREATETRIGGER触发器名称ON表或视图 WITHENCRYPTION 加密 FOR AFTER INSTEADOF 触发器的类型 INSERT UPDATE DELETE ASSQL语句 一条或若干条SQL语句 在触发器中常用的两个IF子句 IFUPDATE 列名 如果在指定的列上进行了INSERT或UPDATE操作 则为真 IF COLUMNS UPDATED 位运算符 位屏蔽码 用于测试插入或更新了表中的哪些列 若某列被插入或更新 则对应位为1 否则为0 最右边的位表示表中的第一列 Inserted表和deleted表 触发器在执行时 会产生两个临时表 inserted表和deleted表 结构上和触发器表的结构相同 自动创建和管理 临时驻留内存 测试数据修改的效果及设置触发器操作的条件执行INSERT操作时 新行被插入到inserted表中执行DELETE操作时 被删除的行被插入到deleted表中执行UPDATE操作时 先删除的旧行被插入deleted表 再插入的新行被插入到inserted表中 例 CREATETRIGGERtrig stuONStudentsAFTERINSERT DELETE UPDATEASSELECT FROMinsertedSELECT FROMdeleted INSERTStudentsVALUES temp temp 男 1900 1 1 1001 DELETEStudentsWHEREStudentID temp2 UPDATEStudentsSETbirthday 1991 1 1 WHEREstudentID temp2 DML触发器的应用 CREATETRIGGERtrig StuNameInsONStudentsFORINSERTASBEGINDECLARE xmnvarchar 30 SELECT xm inserted studentNameFROMinsertedIfEXISTS SELECTstudentNameFROMStudentsWHEREstudentName xm BEGINRAISERROR 姓名不能重复 16 1 ROLLBACKENDEND 例 为SC表创建UPDATE触发器 当修改某个学生的成绩时 修改该学生在STUDENTS表中的平均成绩 CREATETRIGGERTRI SC UPDATEONSCAFTERUPDATEASIF COLUMNS UPDATED 4 0UPDATESTUDENTSSETavgGrade SELECTAVG grade FROMSCWHEREStudentID STUDENTS StudentID FROMSTUDENTS INSERTEDWHEREINSERTED StudentID STUDENTS StudentID INSTEADOF触发器 执行INSTEADOF触发器是为了替换那些初始化触发器的修改语句一个表或视图上只能创建一个INSTEADOF触发器 但可以创建多个AFTER触发器 例 CREATETRIGGERtrig 1ONStudentsINSTEADOFINSERT DELETE UPDATEASBEGINSELECT FROMstudentsSELECT FROMinsertedEND 当添加一行时 新添的行被插入到inserted中 但是并没有插入到Students表中 6 3创建和应用DDL触发器 DDL触发器响应DDL语句 CREATE ALTER和DROP 而激发 可用于管理任务 例如审核和控制数据库操作 DDL触发器一般用于以下目的 1 防止对数据库架构进行某些更改 2 希望数据库中发生某种情况以响应数据库架构中的更改 3 要记录数据库架构中的更改或事件 仅在运行触发DDL触发器的DDL语句后 才会激发 无法作为INSTEADOF触发器使用 语法 CREATETRIGGER触发器名称 FOR AFTER 事件或事件组 n 如DROP TABLE ALTER TABLE CREATE TABLE事件 ASSQL语句 例 CREATETRIGGERtrig SafeStuONDATABASEFORDROP TABLE ALTER TABLEASBEGINPRINT 不能修改表结构 ROLLBACKEND 6 4管理触发器 查看系统存储过程触发器名称Sp help 提供触发器名称 创建日期等sp helptex