忘電子工掌出社 第八章触发器 ·8.1引言 ·8.2触发器工作过程 8.2.1使用 Enterprise Manger创建触发器 8.2.2利用 CREATE TRIGGER命令创建触发 器 8.2.3触发器的工作过程 8.2.4修改、删除触发器
第八章 触发器 • 8．1引言 • 8．2触发器工作过程 • 8.2.1 使用Enterprise Manger创建触发器 • 8.2.2 利用 CREATE TRIGGER 命令创建触发 器 • 8.2.3 触发器的工作过程 • 8.2.4 修改、删除触发器
忘電子工掌出社 81引言 触发器能够实现由主键和外键所不能保证的复杂的参照完整性 和数据的完整性。触发器主要是通过事件进行触发而被执行的。 触发器用于保护表中的数据,当用户对数据表进行修改、添加或 删除时,触发器就会自动运行。维护数据的触发器也可分为三种类 型: INSERT触发器、 UPDATE触发器和 DELETE触发器 在 SQL Sever2000中支持两种类型的触发器:后触发器( After Trigger)和前触发器( Instead Of Trigger)。 AFTER触发器要求只有执行某一操作 INSERT、 UPDATE、DE E 之后,触发器才被触发,且只能在表上定义。 AFTER触发器,可以 定义哪一个触发器被最先触发,哪一个被最后触发,通常使用系统 过程 sp settriggerorder来完成此任务 INSTEAD OF触发器表示并不执行其所定义的操作 INSERT UPDATE、 DELETE,而仅是执行触发器本身。可在表上定义 INSTEAD OF触发器,也可以在视图上定义 INSTEAD OF触发器。但对同一操作 只能定义一个 INSTEAD OF触发器。 返回目录
8.1 引言 触发器能够实现由主键和外键所不能保证的复杂的参照完整性 和数据的完整性。触发器主要是通过事件进行触发而被执行的。 触发器用于保护表中的数据,当用户对数据表进行修改、添加或 删除时，触发器就会自动运行。维护数据的触发器也可分为三种类 型：INSERT触发器、UPDATE触发器和DELETE触发器。 在 SQL Sever 2000 中支持两种类型的触发器：后触发器(After Trigger)和前触发器(Instead Of Trigger)。 AFTER触发器要求只有执行某一操作INSERT、UPDATE 、DELETE 之后，触发器才被触发，且只能在表上定义。 AFTER触发器，可以 定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统 过程 sp_settriggerorder 来完成此任务。 INSTEAD OF触发器表示并不执行其所定义的操作INSERT 、 UPDATE 、DELETE，而仅是执行触发器本身。可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器。但对同一操作 只能定义一个INSTEAD OF触发器。 返回目录
忘電子工掌出社 8.2触发器和工作原理 下面将分别介绍在 SQL Server2000中如何用 Enterprise Manager和T SQL来创建触发器以及触发器的工作原理。 在创建触发器以前必须考虑到以下几个方面: 1) CREATE TRIGGER语句必须是批处理的第一个语句 (2)表的所有者具有创建触发器的缺省权限,表的所有者不能把该 权限传给其它用户。 (3)触发器是数据库对象,所以其命名必须符合命名规则。 (4)触发器只能创建在当前数据库中,只能在基表或在创建视图的 表上创建触发器 (5)一个触发器只能对应一个表。由于 TRUNCATE TABLE语句没 有被记入日志,所以该语句不能触发 DELETE型触发器 WRITETEXT语句不能触发 INSERT或 UPDATE型的触发器 8.2.1使用 Enterprise manger创建触发器 返回目录
8.2 触发器和工作原理 下面将分别介绍在 SQL Server 2000中如何用Enterprise Manager 和 TSQL 来创建触发器以及触发器的工作原理。 • 在创建触发器以前必须考虑到以下几个方面： • （1）CREATE TRIGGER 语句必须是批处理的第一个语句 • （2）表的所有者具有创建触发器的缺省权限，表的所有者不能把该 权限传给其它用户。 • （3）触发器是数据库对象，所以其命名必须符合命名规则。 • （4）触发器只能创建在当前数据库中，只能在基表或在创建视图的 表上创建触发器。 • （5）一个触发器只能对应一个表。由于TRUNCATE TABLE语句没 有被记入日志 ，所 以 该 语 句不 能 触 发 DELETE 型触 发 器 。 WRITETEXT 语句不能触发 INSERT或UPDATE型的触发器。 • 8.2.1 使用Enterprise Manger创建触发器 • 返回目录
忘電子工堂出版 8.2.2利用 CREATE TRIGGER命令创建触发器 CREATE TRIGGER的语法规则如下 CREATE TRIGGER trigger_name on i table view] I WITH ENCRYPTION lalt i FoR I AFTER I INSTEAD OF][[ DELETE][ NSERT]L][ UPDATE] I NOT FOR REPLICATION I AS d IF UPDATE(column)[i AND OR UPDATE (column) n (COLUMNS_UPDATED ) bitwise_operator Updated_bitmask d comparison_operator]column_ bitmask[.n ] sql_ statement[…n] 返回目录
• 8.2.2 利用 CREATE TRIGGER 命令创建触发器 • CREATE TRIGGER的语法规则如下 • 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 ] • }] • sql_statement [ ...n ] • }} 返回目录
忘電子工掌出社 ·各参数的含义说明如下: trigger name:是用户要创建的触发器的名字。 WITH ENCRYPTION:表示对包含有 CREATE TRIGGER文本的 syscomments表进行加密。 AFTER:表示只有在执行了指定的操作Ⅰ NSERT、 DELETE和 UPDATE之后触发器才被激活 INSTEAD OF:指定用触发器中的操作代替触发语句的操作 DELETE][][ NSERT][] LUPDATE]:关键字用来指明哪种数据 操作将激活触发器,至少要指明一个选项 · NOT FOR REPLICATION:表明当复制处理修改与触发器相关联 的表时,触发器不能被执行。 AS:是触发器将要执行的动作。 IF UPDATE column:用来测定对某一确定列是插入操作还是更新 操作,但不与删除操作用在一起。 返回目录
• 各参数的含义说明如下： • trigger_name：是用户要创建的触发器的名字。 • WITH ENCRYPTION：表示对包含有CREATE TRIGGER文本的 syscomments表进行加密。 • AFTER：表示只有在执行了指定的操作INSERT、DELETE和 UPDATE 之后触发器才被激活。 • INSTEAD OF：指定用触发器中的操作代替触发语句的操作。 [DELETE] [,] [INSERT] [,] [UPDATE]：关键字用来指明哪种数据 操作将激活触发器，至少要指明一个选项。 • NOT FOR REPLICATION：表明当复制处理修改与触发器相关联 的表时，触发器不能被执行。 • AS：是触发器将要执行的动作。 • IF UPDATE column：用来测定对某一确定列是插入操作还是更新 操作，但不与删除操作用在一起。 • 返回目录
出 UMNS UPDATED:仅在 INSERT和 UPDATE类型的触发 器中使用,用其来检查所涉及的列是被更新还是被插入 Bitwise operatorj:是在比较中使用的位逻辑运算符 updated_ bitmask:是那些被更新或插入的列的整型位掩码 Comparison operator:是比较操作符 Column bitmask:指那些被检査是否被更新的列的位掩码 ql statement:是包含在触发器中的条件语句或处理语句。n:表 示触发器中可以包含多条TSQL语句。 【例8.1】在 users表上创建一个插入、更新类型的触发器,这个 触发器的名称为tgid_user创建触发器的语句如下 create trigger trg_di_user on users for delete update as sql_ statements return 返回目录
• IF COLUMNS_UPDATED：仅在INSERT和UPDATE类型的触发 器中使用，用其来检查所涉及的列是被更新还是被插入 • Bitwise_operatorj：是在比较中使用的位逻辑运算符。 • updated_bitmask：是那些被更新或插入的列的整型位掩码。 Comparison_operator：是比较操作符。 • Column_bitmask ：指那些被检查是否被更新的列的位掩码。 • Sql_statement：是包含在触发器中的条件语句或处理语句。n：表 示触发器中可以包含多条T_SQL语句。 • 【例8.1】 在 users表上创建一个插入、更新类型的触发器，这个 触发器的名称为trg_id_user创建触发器的语句如下： • create trigger trg_di_user • on users • for delete,update • as sql_statements • return • 返回目录
出子引触发器的工作过程 每个触发器都有两个特殊的表:插入( inserted)表和删除 ( deleted)表。这两个表是逻辑表,并且是由系统管理的。它们 是动态驻留在内存中的,当触发器工作完成,这两个表也被删除。 它们主要保存因用户操作而被影响到的原数据值或新数据值。 1. inserted表的功能 当向表中插入数据时, INSERT触发器触发执行,新的记录插入到 触发器表和 inserted表中。 inserted表就是用来存储向原表插入的内 容 2. deleted表的功能 ·用于保存已从表中删除的记录,当触发一个 DELETED触发器时, 被删除的记录存放到 deleted表中。这样做的目的是,一旦触发器 遇到了强迫它中止的语句被执行时,删除的那些行可以从删除表 中得以恢复。 例如,可用如下语检索ⅲ inserted、 deleted表中的所有记录: select from inserted select from deleted 返回目录
• 8.2.3 触发器的工作过程 • 每个触发器都有两个特殊的表：插入（inserted ）表和删除 （deleted）表。这两个表是逻辑表，并且是由系统管理的。它们 是动态驻留在内存中的，当触发器工作完成，这两个表也被删除。 它们主要保存因用户操作而被影响到的原数据值或新数据值。 • 1. inserted表的功能 • 当向表中插入数据时，INSERT触发器触发执行，新的记录插入到 触发器表和inserted表中。inserted表就是用来存储向原表插入的内 容。 • 2. deleted表的功能 • 用于保存已从表中删除的记录，当触发一个DELETED触发器时， 被删除的记录存放到deleted表中。 这样做的目的是，一旦触发器 遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表 中得以恢复。 • 例如，可用如下语检索inserted、deleted表中的所有记录： • select * from inserted • select * from deleted 返回目录
電子工掌出版杜 ·8.2.4修改、删除触发器 1.修改触发器 通过 Enterprise Manager和TSQL语句或系统过程,可 以修改触发器的名字和正文。 (1)使用 Enterprise Manager修改触发器 返回目录
• 8.2.4 修改、删除触发器 • 1. 修改触发器 • 通过Enterprise Manager和T_SQL语句或系统过程，可 以修改触发器的名字和正文。 • （1）使用 Enterprise Manager 修改触发器 • 返回目录
岀電于工業用 ALTER TRIGGER命令修改触发器,其语法格式为 ALTER TRIGGER trigger_name on(table I view) L WITH ENCRYPTION ti FOR AFTER INSTEAD OFI[ DELETE][,][ INSERT I[ UPDATE] I NOT FOR REPLICATION AS sql_statement[…n]} I FOR AFTER INSTEAD OF IL INSERT][,][ UPDATE]] I NOT FOR REPLICATION AS tIF UPDATE(column) AND I OR) UPDATE(column) COLUMNS UPDATED O bitwise_operator ]updated_bitmask t comparison_operator) column_ bitmask [.n sql_statement[…n] 返回目录
• （2）利用ALTER TRIGGER命令修改触发器, 其语法格式为： • ALTER TRIGGER trigger_name • ON (table | view) • [ WITH ENCRYPTION ] • { { FOR | AFTER | INSTEAD OF { [ DELETE ] [ , ] [ INSERT ] [ , ][ UPDATE ] } • [ NOT FOR REPLICATION ] • AS • sql_statement [ ...n ] } • | { 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_statement [ ...n ] • }} 返回目录
劳缏拥 sp rename命令修改触发器的名字 其语法格式为 sp_rename oldname, newname 2删除触发器 用户在使用完触发器后可以将其删除,只有触发器 所有者才可以删除触发器。删除已创 建的触发器有两种方法: (1)用系统命令 DROP TRIGGER删除指定的触发器, 其语法形式如下: DROP TRIGGER trigger.n 其中参数 trigger指定要删除的触发器名称,包含触发器 所有者名。 (2)删除触发器所在的表时, SQL Server将自动删 除与该表相关的触发器 返回目录
• (3)使用 sp_rename 命令修改触发器的名字 • 其语法格式为 • sp_rename oldname,newname • 2.删除触发器 • 用户在使用完触发器后可以将其删除，只有触发器 所有者才可以删除触发器。删除已创 • 建的触发器有两种方法： • （1） 用系统命令DROP TRIGGER删除指定的触发器， 其语法形式如下： • DROP TRIGGER {trigger}[,…n] • 其中参数trigger指定要删除的触发器名称，包含触发器 所有者名。 • （2）删除触发器所在的表时，SQL Server 将自动删 除与该表相关的触发器。 返回目录