AnQing Teachers College Department of Computer & Information

1. AnQing Teachers College Department of Computer & Information 数据库原理与应用 Principle and Application of Database system 安庆师范学院计算机与信息学院 Principle and Application of Database System

2. 15 触发器 • 触发器是一种特殊的存储过程，其特殊性在于它并不需由用户来直接调用，当对表进行插入、修改、删除操作时自动执行。所以，触发器可以用来实施复杂的完整性约束。 Principle and Application of Database System

3. 触发器的优点 • 可实现数据库中多张表的级联更新。 • 实现比CHECK约束更为复杂的约束。使用CHECK约束，可以限制不满足检查条件的记录输入表中。CHECK约束的检查条件表达式不允许引用其它表中的字段，而触发器可引用其它表中的字段。 Principle and Application of Database System

4. 触发器的触发方式 SQL Server 2000按触发器被激活的时机可分为后触发和替代触发两种触发方式。 • 后触发：当引起触发器执行的更新语句执行完成，并通过各种约束检查后，才执行后触发，这种触发方式称做后触发。创建这种触发器时用AFTER或FOR关键字来指定。后触发只能创建在表上，而不能创建在视图上。 Principle and Application of Database System

5. 替代触发：引起触发器执行的更新语句停止执行，仅执行触发器语句，这种触发方式称做替代触发。创建这种触发器时用INSTEAD OF关键字来指定。替代触发可以创建在表或视图上。 Principle and Application of Database System

6. inserted临时表和deleted临时表 • 每个触发器被激活时，系统都会为它自动创建两个临时表： inserted表和deleted表。这两个表的结构总是与激活触发器的表的结构相同，触发器执行完成后，与该触发器相关的这两个临时表也会被自动删除。 Principle and Application of Database System

7. 在执行DELETE语句删除表中的数据时，系统将数据从表中删除的同时，自动把删除的数据插入到deleted这一系统临时表中；当执行INSERT语句向表中插入数据时，系统将数据插入表的同时，也把相应的数据插入到inserted这一系统临时表中；在执行UPDATE语句修改表数据时，系统先从表中删除表中原有的行，然后再插入新行。其中被删除的行存放在deleted表中，同时插入的新行存放在inserted表中。在执行DELETE语句删除表中的数据时，系统将数据从表中删除的同时，自动把删除的数据插入到deleted这一系统临时表中；当执行INSERT语句向表中插入数据时，系统将数据插入表的同时，也把相应的数据插入到inserted这一系统临时表中；在执行UPDATE语句修改表数据时，系统先从表中删除表中原有的行，然后再插入新行。其中被删除的行存放在deleted表中，同时插入的新行存放在inserted表中。 Principle and Application of Database System

8. 注意： • CREATE TRIGGER语句必须是一个批处理中的第一条语句。 • 触发器只能在当前的数据库创建，但触发器可能引用其它数据对象。 • 如果指定触发器所有者名限定触发器，要以相同方式限定表名。 • 在同一CREATE TRIGGER语句中，可以为多种操作定义相同的触发器操作。 Principle and Application of Database System

9. 如果一个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEAD OF DELETE、INSTEAD OF UPDATE触发器。 • 所有建立和修改数据库及其对象的语句、所有DROP语句都不允许在触发器使用。 • 触发器不返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SET NOCOUNT ON语句以避免返回任何结果集。 Principle and Application of Database System

10. 15.1 利用SQL命令创建触发器 1. 语法格式 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 ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ] /*两个IF子句用于说明触发器执行的条件*/ sql_statements /* T- SQL语句序列*/ Principle and Application of Database System

11. 15.1 利用SQL命令创建触发器 例15.1 对于school数据库，如果在student表中添加或更改数据，则向客户端显示一条信息。 /*使用带有提示消息的触发器*/ USE XSBOOK IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO CREATE TRIGGER reminder ON student FOR INSERT, UPDATE AS RAISERROR (13001, 16, 10) GO 错误级别，取值0—18 状态，取值1—127 错误号，取值13000—2147483647 Principle and Application of Database System

12. 例15.2 在数据库school中创建一触发器，当向score表中插入一记录时，检查该记录的sno在student表是否存在，检查cno号在course表中是否存在，若有一项为否，则不允许插入，并返回“数据不一致”错误信息。 Principle and Application of Database System

13. CREATE TRIGGER TR_score ON score FOR INSERT AS IF 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 Principle and Application of Database System

14. 例15.3 在school数据库的score表上创建一触发器，若对sno列和cno列修改，则给出提示信息，并取消修改操作，用两种方法实现。 Principle and Application of Database System

15. 方法1：用IF UPDATE(column) CREATE TRIGGER TR1_score ON score FOR UPDATE AS IF UPDATE(sno) OR UPDATE(cno) BEGIN RAISERROR('不允许修改',16,1) ROLLBACK TRANSACTION END Principle and Application of Database System

16. 方法2：用IF COLUMNS_UPDATED()函数 CREATE TRIGGER TR2_score ON score FOR UPDATE AS IF (COLUMNS_UPDATED() &3)>0 BEGIN RAISERROR('不允许修改',16,1) ROLLBACK TRANSACTION END Principle and Application of Database System

17. 15.1 利用SQL命令创建触发器 INSTEAD OF触发器的设计 Principle and Application of Database System

18. CREATE VIEW VIEW2 AS SELECT sno,sname,ssex,sbirthday,class FROM student Principle and Application of Database System

19. CREATE TRIGGER INSTEADOFTR_VIEW2 ON VIEW2 INSTEAD OF INSERT AS BEGIN INSERT INTO student(sno,sname,class) SELECT sno,sname,class FROM inserted END Principle and Application of Database System

20. INSERT INTO VIEW2 VALUES('120', '王明', '男', '1975-5-5', '95031') Principle and Application of Database System

21. 向score表插入一条学生选课记录，如果该记录学生成绩低于60分，则把他成绩改为60分。向score表插入一条学生选课记录，如果该记录学生成绩低于60分，则把他成绩改为60分。 Principle and Application of Database System

22. CREATE TRIGGER S1 ON SC FOR INSERT AS BEGIN DECLARE @num1 char(5),@num2 char(10) IF (SELECT Grade FROM inserted)<60 BEGIN SET @num1=(SELECT Sno FROM inserted) SET @num2=(SELECT Cno FROM inserted) UPDATE SC SET Grade=60 WHERE Sno=@num1 AND Cno=@num2 END END Principle and Application of Database System

23. CREATE TRIGGER S1 ON SC INSTEAD OF INSERT AS BEGIN DECLARE @num1 char(10),@num2 char(10) IF (SELECT Grade FROM inserted)<60 BEGIN SET @num1=(SELECT Sno FROM inserted) SET @num2=(SELECT Cno FROM inserted) INSERT INTO SC VALUES(@num1,@num2,60) END END Principle and Application of Database System

24. INSERT INTO SC VALUES('95001','5',50) • INSERT INTO SC VALUES('95001','6',99) Principle and Application of Database System

25. 15.2 利用企业管理器创建触发器 要对school数据库的score表定义DELETE触发器，利用企业管理器实现步骤如下： (1) 进入企业管理器的目录树，展开school数据库的文件夹，选中score表的图标右击，出现一快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，进入触发器属性面。 (2) 在“名称”中，单击“新建”，删除文本框中系统自动产生的内容，然后输入触发器的代码： Principle and Application of Database System

26. 15.3 触发器的修改 1. 利用SQL命令修改触发器 语法格式： ALTER TRIGGER trigger_name ON ( table | 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 } Principle and Application of Database System

27. 15.4 触发器的删除 1. 利用SQL命令删除触发器 语法格式： DROP TRIGGER { trigger } [ ,...n ] 功能：从当前数据库中删除一个或多个触发器。 参数含义： trigger：指要删除的触发器名称。 n：表示可以指定多个触发器。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO 2. 通过企业管理器删除触发器 Principle and Application of Database System