第9章触发器的操作与管理 SQL SERVER2005入门与实例教程电子工业出版社2006
第9章 触发器的操作与管理
触发器概述 触发器是一种特殊的存储过程,它在执行语言事件时自动生效 sQL Server2005包括两大类触发器:DML触发器和DDL触发 (1)DML触发器在数据库中发生数据操作语言(DML)事件 时将启用。DML事件包括在指定表或视图中修改数据的 INSERT语句、 UPDATE语句或 DELETE语句。DML触发器可 以查询其他表,还可以包含复杂的 Transact-SQL语句。将触发 器和触发它的语句作为可在触发器内回滚的单个事务对待。如 果检测到错误(例如,磁盘空间不足),则整个事务即自动回 滚 2)DDL触发器是 SQL Server2005的新增功能。当服务器 或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。 SQL SERVER2005入门与实例教程电子工业出版社2006
触发器概述 •触发器是一种特殊的存储过程，它在执行语言事件时自动生效。 •SQL Server2005 包括两大类触发器：DML 触发器和 DDL 触发 器。 （1）DML 触发器在数据库中发生数据操作语言 (DML) 事件 时将启用。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可 以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发 器和触发它的语句作为可在触发器内回滚的单个事务对待。如 果检测到错误（例如，磁盘空间不足），则整个事务即自动回 滚。 （2）DDL 触发器是 SQL Server 2005 的新增功能。当服务器 或数据库中发生数据定义语言 (DDL) 事件时将调用这些触发器
9.1DML触发器的创建和应用 当数据库中发生数据操作语言(DML)事件时将调用DML触发器 从而确保对数据的处理必须符合由这些SQL语句所定义的规则。 DML触发器的主要优点如下 thor、saes及ocB表中对各配行进们%分 1)DML触发器可通过数据库中的相关表实现级联更改。例如, 以在 titles表的 title id列 入一个删除触发器,以使其他表 的各 匹配行采取删除操作。该触发器用 title id列作为惟一键 (2)DML触发器可以防止恶意或错误的| NSERT、 UPDATE以及 DELETE操作,并强制执行比 CHECK约束定义的限制更为复杂的其 他限制。与 CHECK约束不同,DML触发器可以引用其他表中的列 (3)DML触发器可以评估数据修改前后表的状态,并根据该差异采 取措施。 SQL SERVER2005入门与实例教程电子工业出版社2006
9.1 DML触发器的创建和应用 • 当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。 从而确保对数据的处理必须符合由这些SQL语句所定义的规则。 • DML 触发器的主要优点如下： （1）DML 触发器可通过数据库中的相关表实现级联更改。例如，可 以在 titles 表的 title_id 列上写入一个删除触发器，以使其他表中的各 匹配行采取删除操作。该触发器用 title_id 列作为惟一键，在 titleauthor、sales 及 roysched 表中对各匹配行进行定位。 （2）DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其 他限制。与 CHECK 约束不同，DML 触发器可以引用其他表中的列。 （3）DML 触发器可以评估数据修改前后表的状态，并根据该差异采 取措施
9.1.1DML触发器创建 当创建一个触发器时必须指定如下选项: (1)名称; (2)在其上定义触发器的表; (3)触发器将何时激发; (4)激活触发器的数据修改语句,有效选项为 NSERT、 UPDATE或 DELETE,多个数据修改语句 可激活同一个触发器 (5)执行触发操作的编程语句。 SQL SERVER2005入门与实例教程电子工业出版社2006
9.1.1 DML触发器创建 当创建一个触发器时必须指定如下选项： （1）名称； （2）在其上定义触发器的表； （3）触发器将何时激发； （4）激活触发器的数据修改语句，有效选项为 INSERT、UPDATE 或 DELETE，多个数据修改语句 可激活同一个触发器； （5）执行触发操作的编程语句
9.1.1DML触发器创建 DML触发器使用 deleted和 inserted逻辑表 们在结构上和触发器所在的表的结构相同,SQI Server会自动创建和管理这些表。可以使用这两 仝临时的驻留内存的表测试某些数据修改的效果 及设置触发器操作的条件。 Deleted表用于存储 delete, update语句所影响的 触发器表中删除,并传输到deed表为,行从 的副本。在执行 delete或 update语句时 Inserted表用于存储 Insert或 update语句所影响的 的副本,在一个插入或更新事务处理中,新建 的行被同时添加到 Inserted表和触发器表中。 Inserted表中的行是触发器表中新行的副本。 SQL SERVER2005入门与实例教程电子工业出版社2006
9.1.1 DML触发器创建 • DML 触发器使用 deleted 和 inserted 逻辑表。它 们在结构上和触发器所在的表的结构相同，SQL Server会自动创建和管理这些表。可以使用这两 个临时的驻留内存的表测试某些数据修改的效果 及设置触发器操作的条件。 • Deleted表用于存储delete，update语句所影响的 行的副本。在执行delete或update语句时，行从 触发器表中删除，并传输到deleted表中。 • Inserted表用于存储Insert或update语句所影响的 行的副本，在一个插入或更新事务处理中，新建 的行被同时添加到Inserted表和触发器表中。 Inserted表中的行是触发器表中新行的副本
9.1.1DML触发器创建 使用 SQL Server管理平台创建触发器的过程如下: 在 SQL Server管理平台中,展开指定的服务器和数据库项,然后展开表,选择并 展开要在其上创建1 器的表,如图9-1所 触发器选项,从弹出的快捷 菜单中选择“新建触发器”选项,则 现触发器创建窗口,如图9-2所示。最 后,单击“执行”按钮,即可成功创建触发器。 q日工的 触发器 图9-1新建触发器对话框 图92新建触发器窗口 SQL SERVER2005入门与实例教程电子工业出版社2006
9.1.1 DML触发器创建 使用SQL Server管理平台创建触发器的过程如下： 在SQL Server管理平台中，展开指定的服务器和数据库项，然后展开表，选择并 展开要在其上创建触发器的表，如图9-1所示，右击触发器选项，从弹出的快捷 菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图9-2所示。最 后，单击“执行”按钮，即可成功创建触发器。 图9-1 新建触发器对话框 图9-2 新建触发器窗口
9.1.1DML触发器创建 使用 CREATE TRIGGER命令创建DML触发器的语法形式如 CREATE TRIGGER [schema name. ]trigger_name oN tablel] WITH IENCRYPTION EXECUTE AS Clausell.nJ] IFORAFTERIINSTEAD OF]t[INSERTI L] [UPDATEI L] [DELETEJ] IWITH APPENDI INOT FOR REPLICATION AS sql statement [ ][.nIEXTERNAL NAME ∷= assembly_ name class name. method-name SQL SERVER2005入门与实例教程电子工业出版社2006
9.1.1 DML触发器创建 •使用CREATE TRIGGER命令创建DML触发器的语法形式如 下： CREATE TRIGGER [schema_name.]trigger_name ON {table|view} [WITH [ENCRYPTION] EXECUTE AS Clause][,...n]] {FOR|AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]} [WITH APPEND] [NOT FOR REPLICATION] AS {sql_statement [;] [...n]|EXTERNAL NAME } ::= assembly_name.class_name.method_name
9.1.1DML触发器创建 例9-1示例说明 inserted, deleted表 的作用。执行结果如右图。 rG,db-uery2.sq*要 程序清单如下: nsert into sc values( 313 c1,78 create table sc update sc set sno=3130030101 score= 89 where sno=3130030105' (sno char(10), cno char(2 score rea 结果息 Go 130078 CREATE TRIGGER tr1 ON SC FOR INSERT UP DATE DELETE AS □k m81 PRINT 'inserted表: Select* from inserted PRNT“ deleted表: 11313010c7 Select from deleted Go 图9-3触发器的执行结果 SQL SERVER2005入门与实例教程电子工业出版社2006
9.1.1 DML触发器创建 •例9-1 示例说明inserted，deleted表 的作用。执行结果如右图。 程序清单如下： create table sc (sno char(10), cno char(2), score real) Go CREATE TRIGGER tr1 ON sc FOR INSERT, UPDATE, DELETE AS PRINT ‘inserted表：’ Select * from inserted PRINT ‘deleted表：’ Select * from deleted Go 图9-3 触发器的执行结果
9.1.1DML触发器创建 例9-2创建一个触发器,在s表上创建一个插入 更新类型的触发器。 程序清单如下 CREATE TRIGGER tr s ON S FOR INSERT UPDATE AS Begin DECLARE @bh varchar(6) SELECT@bh= inserted sno FROM inserted/获取 插入或更新操作时的新值(学号) End SQL SERVER2005入门与实例教程电子工业出版社2006
9.1.1 DML触发器创建 •例9-2 创建一个触发器，在 s 表上创建一个插入、 更新类型的触发器。 程序清单如下： CREATE TRIGGER tr_s ON s FOR INSERT, UPDATE AS Begin DECLARE @bh varchar(6) SELECT @bh =inserted.sno FROM inserted /*获取 插入或更新操作时的新值（学号）*/ End
9.1.2DML触发器的应用 1.使用 INSERT触发器 NSERT触发器通常被用来更新时间标记字段,或者验证被触发器监控的字 段中数据满足要求的标准,以确保数据的完整性。 例9-3建立一个触发器,当向sc表中添加数据时,如果添加的数据与s表中的 数据不匹配(没有对应的学号),则将此数据删除。 程序清单如下: CREATE TRIGGER sc ins ON SC FOR INSERT AS BEGIN DECLARE @bh char(5) Select @bh=Insertedsno from Inserted If not exists(select sno from s where s sno=@bh) Delete sc where sno=@bh END SQL SERVER2005入门与实例教程电子工业出版社2006
9.1.2 DML触发器的应用 1. 使用INSERT触发器 INSERT触发器通常被用来更新时间标记字段，或者验证被触发器监控的字 段中数据满足要求的标准，以确保数据的完整性。 例9-3建立一个触发器，当向sc表中添加数据时，如果添加的数据与s表中的 数据不匹配（没有对应的学号），则将此数据删除。 程序清单如下： CREATE TRIGGER sc_ins ON sc FOR INSERT AS BEGIN DECLARE @bh char(5) Select @bh=Inserted.sno from Inserted If not exists(select sno from s where s.sno=@bh) Delete sc where sno=@bh END