实验十 触发器

1、触发器（trigger）是一种特殊的存储过程，不需要由用户调用，当对数据库进行修改（插入、修改、更新）操作时，自动被系统执行。在数据库应用过程中，DBMS需对数据库完整性状况及运行中的其他异常状况发出预警信息并进行相应处理，所以引入触发器，这是一种主动完整性约束机制。触发器实现了数据库管理的ECA（event-condition-action rule ）规则，也称主动数据库规则，即：当发生某一事件时，如果满足给定条件，则执行相应动作。ECA规则体现了DBMS的主动服务性能，即根据发生的事件或数据库状态主动进行相关处理，由触发器实现。触发器的功能及特性：可强制执行较复杂的业务规则或要求。可以禁

2、止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。 可以用来实施复杂的完整性约束，实现数据库中多张表的级联更新。使用CHECK约束，可以限制不满足检查条件的记录输入到表中。CHECK约束的检查条件表达式不允许引用其它表中的字段，而触发器可以引用其它表中的字段，实现比CHECK约束更为复杂的约束。*约束只能通过标准的系统错误信息传递错误信息。如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用触发器。*触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。*触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些

3、表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。通常域完整性可通过 CHECK 约束进行强制，而引用完整性 (RI) 可通过 FOREIGN KEY 约束进行强制。因此，在约束所支持的功能无法满足应用程序的功能要求时，触发器才显得极为有用。 *触发器是数据库语言程序化的一种表征，并且能够保证数据库的一致性，另一方面检测和维护触发器增大了系统开销，降低了修改数据库操作的效率。*在数据库操作中，可以通过关系、触发器、存储过程、应用程序等来实现数据操作，同时规则、约束、缺省值也是保证数据完整性的重要机制

4、。滥用触发器势必影响数据库的结构，并且增加数据库维护的复杂性。触发器的触发方式1、后触发Ø 当引起触发器执行的更新语句执行完成，并通过各种约束检查后，才执行触发称为后触发。Ø 创建后触发方式的触发器时用AFTER或FOR关键字来指定。Ø 后触发只能创建在表上，而不能创建在视图上。2、替代触发Ø 引起触发器执行的更新语句停止执行，仅执行触发器语句，这种触发方式称做替代触发。Ø 创建这种触发器时用INSTEAD OF关键字来指定。Ø 替代触发可以创建在表或视图上。3、inserted临时表和deleted临时表Ø 每个触发器被激

5、活时，系统都会为它自动创建两个临时表： inserted表和deleted表。 当执行INSERT语句向表中插入数据时，系统在将数据插入表的同时，自动把相应的数据插入到inserted这一系统临时表中； 当执行DELETE语句删除表中的数据时，系统在将数据从表中删除的同时，自动把删除的数据插入到deleted这一系统临时表中； 当执行UPDATE语句修改表数据时，系统先从表中删除原有的行，然后再插入新行。其中被删除的行存放在deleted表中，同时插入的新行存放在inserted表中。Ø 触发器执行完成后，与该触发器相关的这两个临时表也会被自动删除。触发器的创建利用SQL命令创建触发

6、器语法格式：CREATE TRIGGER trigger_name ON table | view /*指定触发器名及操作对象*/ WITH ENCRYPTION /*说明是否采用加密方式*/ FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE /*定义触发器的类型*/ NOT FOR REPLICATION /*说明该触发器不用于复制*/ AS  IF UPDATE ( column )  AND | OR UPDATE ( column )   .n  | IF ( COLUMNS_UPDATED (

7、) bitwise_operator updated_bitmask )  comparison_operator column_bitmask .n   /*两个IF子句用于说明触发器执行的条件*/  sql_statements /* T- SQL语句序列*/ （1） FOR触发器的设计【例1】对于school数据库，如果在student表中添加或更改数据，则向客户端显示一条信息。/*使用带有提示消息的触发器*/【例2】在数据库school中创建一触发器，当向score表中插入一记录时，检查该记录的sno在student表是否存在，检查cno号在course表

8、中是否存在，若有一项为否，则不允许插入，并返回“数据不一致”错误信息。CREATE TRIGGER TR_score ON scoreFOR INSERT ASIF NOT EXISTS(SELECT * FROM student WHERE sno =(SELECT sno FROM inserted) OR NOT EXISTS(SELECT * FROM course WHERE cno =(SELECT cno FROM inserted) BEGIN RAISERROR('数据不一致',16,1) ROLLBACK TRANSACTION -回滚事物- END【例3】

9、 在school数据库的score表上创建一触发器，若对sno列和cno列修改，则给出提示信息，并取消修改操作，用两种方法实现。方法1：用IF UPDATE(column)CREATE TRIGGER TR1_score ON scoreFOR UPDATEAS IF UPDATE(sno) OR UPDATE(cno) BEGIN RAISERROR('不允许修改',16,2) ROLLBACK TRANSACTION END方法2：用IF COLUMNS_UPDATED()函数 CREATE TRIGGER TR2_score ON score FOR UPDATE AS

10、IF (COLUMNS_UPDATED() &3)>0 BEGIN RAISERROR('不允许修改',16,1) ROLLBACK TRANSACTION END（2）INSTEAD OF触发器的设计【例4】创建视图VIEW2CREATE VIEW VIEW2ASSELECT sno,sname,ssex,sbirthday,classFROM student创建触发器INSTEADOFTR_VIEW2：CREATE TRIGGER INSTEADOFTR_VIEW2 ON VIEW2INSTEAD OF INSERTAS BEGININSERT INTO st

11、udent(sno,sname,class)SELECT sno,sname,class FROM insertedENDINSERT INTO VIEW2 VALUES('120', '王明', '男', '1975-5-5', '95031')【例5】向score表插入一条学生选课记录，如果该记录学生成绩低于60分，则把他成绩改为60分。CREATE TRIGGER S1 ON scoreFOR INSERT ASBEGINDECLARE num1 char(5),num2 char(10) IF (SELEC

12、T degree FROM inserted)<60 BEGIN SET num1=(SELECT sno FROM inserted) SET num2=(SELECT cno FROM inserted) UPDATE score SET degree=60 WHERE sno=num1 AND cno=num2 ENDENDv INSERT INTO score VALUES('95001','5',50)CREATE TRIGGER S2 ON scoreINSTEAD OF INSERT ASBEGINDECLARE num1 char(10),

13、num2 char(10) IF (SELECT degree FROM inserted)<60 BEGIN SET num1=(SELECT sno FROM inserted) SET num2=(SELECT cno FROM inserted) INSERT INTO score VALUES(num1,num2,60) END ELSE INSERT INTO score SELECT * FROM insertedENDv INSERT INTO score VALUES('95001','6',99)v 注意：1. CREATE TRIGG

14、ER语句必须是一个批处理中的第一条语句。2. 触发器只能在当前的数据库创建，但触发器可能引用其它数据库对象。3. 如果指定触发器所有者名限定触发器，要以相同方式限定表名。4. 在同一CREATE TRIGGER语句中，可以为多种操作定义相同的触发器操作。5. 如果一个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEAD OF DELETE、INSTEAD OF UPDATE触发器。6. 所有建立和修改数据库及其对象的语句、所有DROP语句都不允许在触发器中使用。7. 触发器不返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋

15、值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SET NOCOUNT ON语句以避免返回任何结果集。8. 如果触发器表上存在约束，则在 INSTEAD OF 触发器执行后但在 AFTER 触发器执行前检查这些约束。如果约束破坏，则回滚 INSTEAD OF 触发器操作并且不执行 AFTER 触发器。 v 不能在触发器中使用的语句： 触发器中可以使用大多数 T-SQL 语句，但如下一些语句不能在触发器中使用： 1. CREATE 语句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。 2. ALTER 语句，如：ALTER DATABAS

16、E、ALTER TABLE、ALTER INDEX 等。 3. DROP 语句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。 4. DISK 语句，如：DISK INIT、DISK RESIZE。 5. LOAD 语句，如：LOAD DATABASE、LOAD LOG。 6. RESTORE 语句，如：RESTORE DATABASE、RESTORE LOG。 7. RECONFIGURE语句。8. TRUNCATE TABLE 语句在Sybase的触发器中不可使用！ 触发器的管理1、查看触发器v 系统存储过程sp_help、sp_helptext、sp_d

17、epends分别提供有关触发器的不同信息。 sp_help：查看触发器名称、属性、类型、创建时间。 语法： EXEC sp_help trigger_name sp_helptext：查看触发器正文。 sp_depends：查看触发器引用的关系及其相关触发器信息。v 使用管理器窗口查看触发器信息 步骤： （1）右击“数据库” “表” “触发器”，在弹出的快捷菜单中选择“编写触发器脚本为” “CREAT到” “新查询编辑器窗口”； （2）在出现的触发器编辑窗口可查看选定触发器的信息。2、利用SQL命令修改触发器语法格式：ALTER TRIGGER trigger_name ON ( table

18、| view ) WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOT FOR REPLICATION  AS     sql_statements    | ( FOR | AFTER | INSTEAD OF ) INSERT , UPDATE       NOT FOR REPLICATION      AS      IF UPDATE ( column )   AND | OR UPDATE ( column )   .n        | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n             sql_statements   3、利用