触发器精讲.doc

第十五章 触发器触发器是一种特殊的存储过程，其特殊性在于它并不需由用户来直接调用，当对表进行插入、修改、删除操作时自动执行。所以，触发器可以用来实施复杂的完整性约束。触发器的优点1. 可实现数据库中多张表的级联更新。2. 实现比CHECK约束更为复杂的约束。使用CHECK约束，可以限制不满足检查条件的记录输入表中。CHECK约束的检查条件表达式不允许引用其它表中的字段，而触发器可引用其它表中的字段。触发器的触发方式SQL Server 2000按触发器被激活的时机可分为后触发和替代触发两种触发方式。1. 后触发：当引起触发器执行的更新语句执行完成，并通过各种约束检查后，才执行后触发，这种触发方式称做后触发。创建这种触发器时用AFTER或FOR关键字来指定。后触发只能创建在表上，而不能创建在视图上。2. 替代触发：引起触发器执行的更新语句停止执行，仅执行触发器语句，这种触发方式称做替代触发。创建这种触发器时用INSTEAD OF关键字来指定。替代触发可以创建在表或视图上。inserted临时表和deleted临时表每个触发器被激活时，系统都会为它自动创建两个临时表： inserted表和deleted表。这两个表的结构总是与激活触发器的表的结构相同，触发器执行完成后，与该触发器相关的这两个临时表也会被自动删除。在执行DELETE语句删除表中的数据时，系统将数据从表中删除的同时，自动把删除的数据插入到deleted这一系统临时表中；当执行INSERT语句向表中插入数据时，系统将数据插入表的同时，也把相应的数据插入到inserted这一系统临时表中；在执行UPDATE语句修改表数据时，系统先从表中删除表中原有的行，然后再插入新行。其中被删除的行存放在deleted表中，同时插入的新行存放在inserted表中。n 注意：1. CREATE TRIGGER语句必须是一个批处理中的第一条语句。2. 触发器只能在当前的数据库创建，但触发器可能引用其它数据对象。3. 如果指定触发器所有者名限定触发器，要以相同方式限定表名。4. 在同一CREATE TRIGGER语句中，可以为多种操作定义相同的触发器操作。5. 如果一个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEAD OF DELETE、INSTEAD OF UPDATE触发器。6. 所有建立和修改数据库及其对象的语句、所有DROP语句都不允许在触发器使用。7. 触发器不返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SET NOCOUNT ON语句以避免返回任何结果集。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语句序列*/例15.1 对于school数据库，如果在student表中添加或更改数据，则向客户端显示一条信息。/*使用带有提示消息的触发器*/USE XSBOOKIF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER reminder ON student FOR INSERT, UPDATE AS RAISERROR (13001, 16, 10)GO例15.2 在数据库school中创建一触发器，当向score表中插入一记录时，检查该记录的sno在student表是否存在，检查cno号在course表中是否存在，若有一项为否，则不允许插入，并返回“数据不一致”错误信息。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例15.3 在school数据库的score表上创建一触发器，若对sno列和cno列修改，则给出提示信息，并取消修改操作，用两种方法实现。方法1：用IF UPDATE(column)CREATE TRIGGER TR1_score ON scoreFOR UPDATEAS IF UPDATE(sno) OR UPDATE(cno) BEGIN RAISERROR(不允许修改,16,1) ROLLBACK TRANSACTION END方法2：用IF COLUMNS_UPDATED()函数CREATE TRIGGER TR2_score ON scoreFOR UPDATEAS IF (COLUMNS_UPDATED() &3)0 BEGIN RAISERROR(不允许修改,16,1) ROLLBACK TRANSACTION ENDINSTEAD OF触发器的设计CREATE VIEW VIEW2ASSELECT sno,sname,ssex,sbirthday,classFROM studentCREATE TRIGGER INSTEADOFTR_VIEW2 ON VIEW2INSTEAD OF INSERTAS BEGININSERT INTO student(sno,sname,class)SELECT sno,sname,class FROM insertedENDINSERT INTO VIEW2 VALUES(120, 王明, 男, 1975-5-5, 95031)向score表插入一条学生选课记录，如果该记录学生成绩低于60分，则把他成绩改为60分。CREATE TRIGGER S1 ON scoreFOR INSERT ASBEGINDECLARE num1 char(5),num2 char(10) IF (SELECT 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 ENDENDCREATE TRIGGER S1 ON scoreINSTEAD OF INSERT ASBEGINDECLARE num1 char(10),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) ENDENDINSERT INTO SC VALUES(95001,5,50)INSERT INTO SC VALUES(95001,6,99)15.2 利用企业管理器创建触发器要对school数据库的score表定义DELETE触发器，利用企业管理器实现步骤如下： (1) 进入企业管理器的目录树，展开school数据库的文件夹，选中score表的图标右击，出现一快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，进入触发器属性面。(2) 在“名称”中，单击“新建”，删除文本框中系统自动产生的内容，然后输入触发器的代码：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 15.4 触发器的删除1. 利用SQL命令删除