第十五章触发器触发器是一种特殊的存储过程，其特殊性在于它并不需由用户来直接调用，当对表进行插入、修改、删除操作时自动执行。所以，触发器可以用来实施复杂的完整性约束。触发器的优点1.可实现数据库中多张表的级联更新。2．实现比CHECK约束更为复杂的约束。使用CHECK约束，可以限制不满足检查条件的记录输入表中。CHECK约束的检查条件表达式不允许引用其它表中的字段，而触发器可引用其它表中的字段。触发器的触发方式SQLServer2000按触发器被激活的时机可分为后触发和替代触发两种触发方式。1．后触发：当引起触发器执行的更新语句执行完成，并通过各种约束检查后，才执行后触发，这种触发方式称做后触发。创建这种触发器时用AFTER或FOR关键字来指定。后触发只能创建在表上，而不能创建在视图上。2替代触发：引起触发器执行的更新语句停止执行，仅执行触发器语句，这种触发方式称做替代触发。创建这种触发器时用INSTEADOF关键字来指定。替代触发可以创建在表或视图上。inserted临时表和deleted临时表每个触发器被激活时，系统都会为它自动创建两个临时表：inserted表和deleted表。这两个表的结构总是与激活触发器的表的结构相同，触发器执行完成后，与该触发器相关的这两个临时表也会被自动删除。在执行DELETE语句删除表中的数据时，系统将数据从表中删除的同时，自动把删除的数据插入到deleted这一系统临时表中；当执行INSERT语句向表中插入数据时，系统将数据插入表的同时，也把相应的数据插入到inserted这一系统临时表中：在执行UPDATE语句修改表数据时，系统先从表中删除表中原有的行，然后再插入新行。其中被删除的行存放在deleted表中，同时插入的新行存放在inserted表中。■注意：1.CREATETRIGGER语句必须是一个批处理中的第一条语句。2.触发器只能在当前的数据库创建，但触发器可能引用其它数据对象。3.如果指定触发器所有者名限定触发器，要以相同方式限定表名。4.在同一CREATETRIGGER语句中，可以为多种操作定义相同的触发器操作。5.如果一个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。6.所有建立和修改数据库及其对象的语句、所有DROP语句都不充许在触发器使用。7.触发器不返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SETNOCOUNTON语句以避免返回任何结果集。15.1利用SQL命令创建触发器1.语法格式CREATETRIGGERtrigger_nameON（table|view）/*指定触发器名及操作对象*/[WITHENCRYPTION]/*说明是否采用加密方式*/
第十五章 触发器 触发器是一种特殊的存储过程，其特殊性在于它并不需由用户来直接调用，当对表进行 插入、修改、删除操作时自动执行。所以，触发器可以用来实施复杂的完整性约束。 触发器的优点 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 表中。 ◼ 注意： 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|INSTEADOF[DELETE][][INSERT][][UPDATE]/*定义触发器的类型*//*说明该触发器不用[NOTFORREPLICATION]于复制*/AS[(IFUPDATE(column)[(AND|OR) UPDATE(column)][...n]IF（COLUMNS_UPDATED（）（bitwise_operatorupdatedbitmask）(comparison_operator)column_bitmask[...n]1/*两个IF子句用于说明触发器执行的条件*//*T-SQL语句序列*/sql_statements例15.1对于school数据库，如果在student表中添加或更改数据，则向客户端显示一条信息。/*使用带有提示消息的触发器*/USEXSBOOKIFEXISTS(SELECTnameFROMsysobjectsWHEREname='reminderANDtype=TR')DROP TRIGGER reminderGOCREATE TRIGGER reminder ON studentFORINSERT,UPDATEASRAISERROR(13001,16,10)GO例15.2在数据库school中创建一触发器，当向score表中插入一记录时，检查该记录的sno在student表是否存在，检查cno号在course表中是否存在，若有一项为否，则不允许插入，并返回“数据不一致”错误信息。CREATETRIGGER TR score ON scoreFORINSERTASIFNOTEXISTS(SELECT*FROMstudentWHEREsno=(SELECTsnoFROM inserted))ORNOT EXISTS(SELECT*FROM course WHERE cno =(SELECTcno FROM inserted)BEGINRAISERROR(数据不一致，16,1)ROLLBACKTRANSACTIONEND例15.3在school数据库的score表上创建一触发器，若对sno列和cno列修改，则给出提示信息，并取消修改操作，用两种方法实现。方法1：用IFUPDATE(column)CREATETRIGGERTRI score ONscoreFORUPDATEASIFUPDATE(sno)ORUPDATE(cno)BEGIN
{ 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 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 例 15.2 在数据库 school 中创建一触发器，当向 score 表中插入一记录时，检查该记录的 sno 在 student 表是否存在，检查 cno 号在 course 表中是否存在，若有一项为否，则不允许插入， 并返回“数据不一致”错误信息。 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 例15.3 在 school 数据库的 score 表上创建一触发器，若对 sno 列和 cno 列修改，则给出提 示信息，并取消修改操作，用两种方法实现。 方法 1：用 IF UPDATE(column) CREATE TRIGGER TR1_score ON score FOR UPDATE AS IF UPDATE(sno) OR UPDATE(cno) BEGIN
RAISERROR(不允许修改16,1)ROLLBACK TRANSACTIONEND方法2：用IFCOLUMNSUPDATED函数CREATETRIGGERTR2 scoreONscoreFORUPDATEASIF(COLUMNS_UPDATEDO&3)>0BEGINRAISERROR(不允许修改，16,1)ROLLBACKTRANSACTIONENDINSTEADOF触发器的设计CREATEVIEWVIEW2ASSELECT sno,sname,ssex,sbirthday,classFROM studentCREATETRIGGERINSTEADOFTRVIEW2ONVIEW2INSTEADOFINSERTASBEGININSERT INTO student(sno,sname,class)SELECT sno,sname,class FROM insertedENDINSERTINTOVIEW2VALUES(120王明男1975-5-595031)向score表插入一条学生选课记录，如果该记录学生成绩低于60分，则把他成绩改为60分。CREATETRIGGERS1ONscoreFORINSERTASBEGINDECLARE @num1 char(5),@num2 char(10)IF(SELECTdegreeFROM inserted)<60BEGINSET@num1=(SELECTsnoFROMinserted)SET@num2=(SELECTcnoFROM inserted)UPDATEscoreSETdegree=60WHEREsno=@numlANDcno=@num2ENDENDCREATETRIGGERS1ONscoreINSTEADOFINSERT
RAISERROR('不允许修改',16,1) ROLLBACK TRANSACTION END 方法 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 INSTEAD OF 触发器的设计 CREATE VIEW VIEW2 AS SELECT sno,sname,ssex,sbirthday,class FROM student CREATE TRIGGER INSTEADOFTR_VIEW2 ON VIEW2 INSTEAD OF INSERT AS BEGIN INSERT INTO student(sno,sname,class) SELECT sno,sname,class FROM inserted END INSERT INTO VIEW2 VALUES('120', '王明', '男', '1975-5-5', '95031') 向 score 表插入一条学生选课记录，如果该记录学生成绩低于 60 分，则把他成绩改为 60 分。 CREATE TRIGGER S1 ON score FOR INSERT AS BEGIN DECLARE @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 END END CREATE TRIGGER S1 ON score INSTEAD OF INSERT
ASBEGINDECLARE@num1char(10),@num2char(10)IF (SELECT degreeFROM inserted)<60BEGINSET@num1=(SELECTsnoFROM inserted)SET@num2-(SELECTcnoFROMinserted)INSERTINTOscoreVALUES(@num1,@num2,60)ENDENDINSERTINTOSCVALUES(95001550INSERTINTOSCVALUES(95001,6,99)利用企业管理器创建触发器15.2要对school数据库的score表定义DELETE触发器，利用企业管理器实现步骤如下：（1）进入企业管理器的目录树，展开school数据库的文件夹，选中score表的图标右击，出现一快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，进入触发器属性面。(2）在“名称"中，单击“新建”，删除文本框中系统自动产生的内容，然后输入触发器的代码：胜发器属性常规名称(N)新建A文本(）TRIGGER[TRIGGERNAME]ON[dbo][SCOre]BEAT一ORINSERT.UPDATE,DELETE-43,3/3無除D检查语法C另存为模板(S)确定关闭应用帮助15.3触发器的修改1.利用SQL命令修改触发器语法格式：ALTERTRIGGERtriggernameON(table/view)[WITH ENCRYPTION ]((FOR|AFTER|INSTEADOF([DELETE][,][INSERT][,][UPDATE ])[NOT FOR REPLICATION ]ASsql_statements
AS BEGIN DECLARE @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) END END INSERT 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
1I(FOR|AFTER|INSTEAD OF)([INSERT][,][UPDATE ])[NOTFORREPLICATION]AS(IFUPDATE（column)[(AND|OR) UPDATE(column)][..n][IF（COLUMNS_UPDATED（）（bitwise_operator）updated_bitmask)( comparison_operator ] column_bitmask [...n ]sql_statements115.4触发器的删除1.利用SQL命令删除触发器语法格式：DROPTRIGGER (trigger)[,..n]功能：从当前数据库中删除一个或多个触发器。参数含义：trigger：指要删除的触发器名称。n：表示可以指定多个触发器。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname='reminderANDtype='TR')DROPTRIGGERreminderGO2.通过企业管理器剧除触发器
} | { ( 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 命令删除触发器 语法格式： 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. 通过企业管理器删除触发器