触发器综合实例精选文档.ppt

《触发器综合实例精选文档.ppt》由会员分享，可在线阅读，更多相关《触发器综合实例精选文档.ppt（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、触发器综合实例本讲稿第一页，共十五页触发器（触发器（trigger）是一种特殊的存储过程，）是一种特殊的存储过程，它不同于一般的存储过程：它不同于一般的存储过程：一般的存储过程通过存储过程名称被直接调一般的存储过程通过存储过程名称被直接调用用触发器主要是通过事件进行触发而被执行。触发器主要是通过事件进行触发而被执行。本讲稿第二页，共十五页触发器的主要作用是实现由主键和外键等约束所不能保证的复杂的参照完整性和数据一致性。除此之外，触发器还有以下作用：1.触发器可以对数据库进行级联修改。2.实现比CHECK约束更为复杂的限制。3.比较数据修改前后的差别。4.强制表的修改要合乎业务规则。本讲稿第三页

2、，共十五页按照触发响应时间按的不同按照触发响应时间按的不同可以将触发器分为两大类：后触发器可以将触发器分为两大类：后触发器和前触发器。和前触发器。（1）后触发器（）后触发器（After Trigger）：）：这种类型的触发器将在数据变动（这种类型的触发器将在数据变动（UPDATE、INSERT和和DELETE操作）完成后才被触发。操作）完成后才被触发。指定指定 AFTER 与指定与指定 FOR 相同。相同。AFTER触发器只能在表上定义。触发器只能在表上定义。在同一个数据表中可以创建多个在同一个数据表中可以创建多个AFTER触发器。触发器。默认的为默认的为AFTER触发器。触发器。本讲稿第四页

3、，共十五页按照触发响应时间按的不同按照触发响应时间按的不同可以将触发器分为两大类：后触发可以将触发器分为两大类：后触发器和前触发器。器和前触发器。（2）前触发器（）前触发器（Inserted Of Trigger）：）：INSTEAD OF触触发发器器在在数数据据变变动动以以前前被被触触发发，并并取取代代变变动动数数据据的的操操作作（UPDATE、INSERT和和DELETE操操作作），而而去去执执行行触触发发器器定义的操作。定义的操作。INSTEAD OF触发器可以在表或视图上定义。触发器可以在表或视图上定义。在在表表或或视视图图上上，每每个个UPDATE、INSERT和和DELETE语语句

4、句最最多可以定义一个多可以定义一个INSTEAD OF触发器。触发器。本讲稿第五页，共十五页语法格式：语法格式：CREATE TRIGGER schema_name.trigger_name ON table|view /*指定操作对象指定操作对象*/WITH ENCRYPTION/*说明是否采用加密方式说明是否采用加密方式*/FOR|AFTER|INSTEAD OF INSERT ,UPDATE ,DELETE WITH APPEND NOT FOR REPLICATION /*说明该触发器不用于复制说明该触发器不用于复制*/AS sql_statement ;.n|EXTERNAL NAM

5、E asse MBly_name.class_name.method_name 本讲稿第六页，共十五页触发器中使用的特殊表触发器中使用的特殊表当触发器引用视图或者临时表，并产生两个特当触发器引用视图或者临时表，并产生两个特殊的表：殊的表：deleted表和表和inserted表。表。执行执行INSERT语句或语句或UPDATE语句向表中插入数据行时，插入语句向表中插入数据行时，插入到触发器表中的行作为复本同时将被插入到到触发器表中的行作为复本同时将被插入到inserted表中；表中；执行执行DELETE语句删除表中数据时，触发表中被删除的行作语句删除表中数据时，触发表中被删除的行作为复本被插入

6、到为复本被插入到deleted表中；表中；执行执行UPDATE语句修改数据时，先删除触发表中的旧行，并将语句修改数据时，先删除触发表中的旧行，并将该旧行插入该旧行插入deleted表中，再插入新行，同时将新行插入到表中，再插入新行，同时将新行插入到inserted表中。表中。本讲稿第七页，共十五页触发器的主要作用是实现由主键和外键等约束所不能保证的复杂的参照完整性和数据一致性。除此之外，触发器还有以下作用：1.触发器可以对数据库进行级联修改。2.实现比CHECK约束更为复杂的限制。3.比较数据修改前后的差别。4.强制表的修改要合乎业务规则。本讲稿第八页，共十五页使用触发器实现复杂的参照完整性和

7、数据一致性。例1:若修改SC中一个记录的学号,则要检查S中是否存在与该学号相同的记录,若有则不允许修改,若没有则可以修改.本讲稿第九页，共十五页CREATE TRIGGER TRIGGER_SC ON dbo.sc FOR UPDATEASIF UPDATE(SNO)BEGINDECLARE SNO_NEW CHAR(2),SNO_OLD CHAR(2),SNO_CNT INTSELECT SNO_OLD=SNO FROM DELETEDSELECT SNO_CNT=COUNT(*)FROM S WHERE SNO=SNO_OLDIF SNO_CNT0 ROLLBACK TRANSACTION

8、END本讲稿第十页，共十五页使用触发器对数据库进行级联修改。例2.若修改表S中一学生的学号,则表SC中与该学生相关的学号被自动修改.CREATE TRIGGER TRIGGER_S ON dbo.s FOR UPDATEAS IF UPDATE(SNO)BEGINDECLARE SNO_NEW CHAR(2),SNO_OLD CHAR(2)SELECT SNO_NEW=SNO FROM INSERTEDSELECT SNO_OLD=SNO FROM DELETEDUPDATE SC SET SNO=SNO_NEW WHERE SNO=SNO_OLDEND本讲稿第十一页，共十五页使用触发器对数据

9、库进行级联修改。例3：为C表创建一个级联删除触发器:通过课程名从C中删除某课程信息,同时删除表SC中与此课程相关的选课记录.CREATE TRIGGER TRIGGER_C ON dbo.c FOR DELETEASDECLARE CNO CHAR(2)SELECT CNO=CNO FROM DELETEDDELETE FROM SCWHERE CNO=CNO本讲稿第十二页，共十五页使用触发器实现比CHECK约束更为复杂的限制例4：为SC表创建一触发器,当插入一个记录或修改成绩时,确保此记录的成绩为0100分.本讲稿第十三页，共十五页CREATE TRIGGER TRIGGER_SC1 ON

10、dbo.sc FOR INSERT,UPDATEASDECLARE SCORE TINYINTSELECT SCORE=SCORE FROM INSERTEDIF SCORE0 AND SCORE=100 BEGIN PRINT 操作完成 return endprint 成绩超出0-100rollback transactiongo本讲稿第十四页，共十五页Insert into sc values(2,4,80)Go结果：操作完成(1 行受影响)Insert into sc values(2,5,101)Go成绩超出-100消息3609，级别16，状态1，第1 行事务在触发器中结束。批处理已中止。本讲稿第十五页，共十五页