第9章触发器 9.1触发器的概念 9.2触发器的种类与工作原理 9.3创建触发器 94查看、修改触发器 9.5删除触发器
第9章 触发器 9.1 触发器的概念 9.2 触发器的种类与工作原理 9.3 创建触发器 9.4 查看、修改触发器 9.5 删除触发器
9.1触发器的概念 SQL Server中除可以使用约東和规则强制实施数 据完整性外,还可以使用触发器实现数据的完整性 第触发器( TRIGGER)是一种实施数据完整性的特殊 类型存储过程,它不同于前面介绍过的存储过程,兼 有强制参照完整性和存储过程的双重特性。在对表执 发行 UPDATE、 INSERT、 DELETE命令时, SQL Server就会 器自动触发执行触发器所定义的SQL语句,从而确保对 数据的处理必须符合由这些SQL语句所定义的规贝 这相当于执行了一个存储过程。但触发器与一般的存 储过程又有不同,存储过程是数据库对象,一般由用 户直接调用执行(除自动执行存储过程);而触发器 依赖于数据表,只能通过操作 UPDATE、 INSERT DELETE等命令触发,用户不能直接调用执行
9.1 触发器的概念 SQL Server中除可以使用约束和规则强制实施数 据完整性外，还可以使用触发器实现数据的完整性。 触发器（TRIGGER）是一种实施数据完整性的特殊 类型存储过程，它不同于前面介绍过的存储过程，兼 有强制参照完整性和存储过程的双重特性。在对表执 行UPDATE、INSERT、DELETE命令时，SQL Server就会 自动触发执行触发器所定义的SQL语句，从而确保对 数据的处理必须符合由这些SQL语句所定义的规则， 这相当于执行了一个存储过程。但触发器与一般的存 储过程又有不同，存储过程是数据库对象，一般由用 户直接调用执行（除自动执行存储过程）；而触发器 依赖于数据表，只能通过操作UPDATE 、INSERT 、 DELETE等命令触发，用户不能直接调用执行。 第 9 章 触 发 器
9.1触发器的概念 触发器的主要功能是能够实现由主键与外键所不能保证的复 第杂的参照完整性和数据的一致性。此外,还有以下功能 (1)强化约束。触发器能够实现比 CHECK语句更为复杂的 约束 触 (2)跟踪执行。触发器可以侦测数据库中内在的操作,从 燃而不允许未经许可的插入、更新和删除操作影响数据库 (3)级联运行。触发器可以侦测数据库内的操作,并自动 地级联影响整个数据库中的各项内容 (4)存储过程的调用。为了响应数据库更新,触发器可以 调用一个或多个存储过程 因此,触发器可以解决高级形式的业务规则或复杂的行为限 制以及实现定制记录等方面的问题。触发器能够根据某表数据 修改前后状态所发生的差异,执行相应的处理。同时一个表的 同一类型( INSERT、 UPDATE、 DELETE)的多个触发器能够对同 种数据操作采取多种不同的处理
9.1 触发器的概念 触发器的主要功能是能够实现由主键与外键所不能保证的复 杂的参照完整性和数据的一致性。此外，还有以下功能： （1）强化约束。触发器能够实现比CHECK 语句更为复杂的 约束。 （2）跟踪执行。触发器可以侦测数据库中内在的操作，从 而不允许未经许可的插入、更新和删除操作影响数据库。 （3）级联运行。触发器可以侦测数据库内的操作，并自动 地级联影响整个数据库中的各项内容。 （4）存储过程的调用。为了响应数据库更新，触发器可以 调用一个或多个存储过程。 因此，触发器可以解决高级形式的业务规则或复杂的行为限 制以及实现定制记录等方面的问题。触发器能够根据某表数据 修改前后状态所发生的差异，执行相应的处理。同时一个表的 同一类型（INSERT、UPDATE、DELETE）的多个触发器能够对同 一种数据操作采取多种不同的处理。 第 9 章 触 发 器
9.2触发器的种类与工作原理 9.2.1触发器的种类 SQL Server2000支持两种类型的触发器: AFTER触发器和 第 INSTEAD OF1触发器。 AFTER触发器 AFTER触发器即为 SQL Server2000版本以前所介绍的触发 触器。该类型触发器要求只在执行了某一操作( INSERT、 UPDATE DELETE)之后,触发器才被触发,且只能在表上定义。可以为 针对表的同一操作定义多个触发器。对于 AFTER触发器,可以 定义哪一个触发器被最先触发,哪一个被最后触发,通常使用 系统过程 sp settriggerorder来完成此任务。 INSTEAD OF触发器 INSTEAD OF触发器表示并不执行命令所定义的操作( INSERT UPDATE、 DELETE),而仅是执行触发器中定义的代码。它既可 在表上定义,也可以在视图上定义,但对同一操作只能定义 个 NSTEAD O触发器
9.2 触发器的种类与工作原理 9.2.1 触发器的种类 SQL Server 2000支持两种类型的触发器：AFTER触发器和 INSTEAD OF触发器。 l AFTER 触发器 AFTER 触发器即为SQL Server 2000版本以前所介绍的触发 器。该类型触发器要求只在执行了某一操作（INSERT、UPDATE、 DELETE）之后，触发器才被触发，且只能在表上定义。可以为 针对表的同一操作定义多个触发器。对于AFTER触发器，可以 定义哪一个触发器被最先触发，哪一个被最后触发，通常使用 系统过程sp_settriggerorder 来完成此任务。 l INSTEAD OF触发器 INSTEAD OF触发器表示并不执行命令所定义的操作（INSERT、 UPDATE、DELETE），而仅是执行触发器中定义的代码。它既可 在表上定义，也可以在视图上定义，但对同一操作只能定义一 个INSTEAD OF触发器。 第 9 章 触 发 器
9.2触发器的种类与工作原理 9.2.2触发器的工作原理 当向表执行 INSERT、 UPDATEI或 DELETE语句时,若该 第表设置了触发器,则 SQL Server将根据不同的操作自 动生成一个或二个临时表: Inserted表或 Deleted表 同时将操作数据送入触发器表、 Inserted表或 发 Deleted表中。 Inserted表和 Deleted表是 SQL Server为触发器 句创建的逻辑表,存储在内存中,不是存储在数据库 中,因此不允许用户直接对其修改。它们的结构与定 义触发器相关表的结构相同,用于保存用户操作可能 改变的行的新值或旧值,由系统自动维护。当触发器 工作完成后,与之相关的逻辑表将自动删除
9.2 触发器的种类与工作原理 9.2.2 触发器的工作原理 当向表执行INSERT、UPDATE或DELETE语句时，若该 表设置了触发器，则SQL Server将根据不同的操作自 动生成一个或二个临时表：Inserted表或Deleted表， 同 时 将 操 作 数 据 送 入 触 发 器 表 、 Inserted 表 或 Deleted表中。 Inserted表和Deleted表是SQL Server为触发器语 句创建的逻辑表，存储在内存中，不是存储在数据库 中，因此不允许用户直接对其修改。它们的结构与定 义触发器相关表的结构相同，用于保存用户操作可能 改变的行的新值或旧值，由系统自动维护。当触发器 工作完成后，与之相关的逻辑表将自动删除。 第 9 章 触 发 器
9.3创建触发器 触发器既可以使用TSQL语句创建,也可以在企业 管理器中创建。 创建触发器时,需要确定触发器的以下三个组成部 章分 阅·确定在其上定义触发器的表; ●触发器将何时激活。指明激活触发器的数据修 改语句,有效选项为 INSERT、 UPDATE或 DELETE 多个数据修改语句可激活同一个触发器; ●执行触发操作所执行的TSQL语句
9.3 创建触发器 触发器既可以使用T-SQL语句创建，也可以在企业 管理器中创建。 创建触发器时，需要确定触发器的以下三个组成部 分： l 确定在其上定义触发器的表； l 触发器将何时激活。指明激活触发器的数据修 改语句，有效选项为INSERT、UPDATE 或DELETE。 多个数据修改语句可激活同一个触发器； l 执行触发操作所执行的T-SQL语句。 第 9 章 触 发 器
9.3创建触发器 9.3.1使用TSQL语句创建触发器 创建触发器的 CREATE TRIGGER语句的语法格式如下: CREATE TRIGGER trigger-name 第 oN table name view name a [WITH ENCRYPTIONI II FOR AFTER INSTEAD OF3 [NSERTLI 触[ DELETEILI[UPDATE 发 I NOT FOR REPLICATION AS [IF UPDATE( column) [AND OR ]UPDATE( column][.n] IF COLUMNS_ UPDATED()i bitwise_operator y updated bitmask) i comparison_operator)column_bitmask[.n] 3] sql statement[.nI } ○
9.3 创建触发器 9.3.1 使用T-SQL语句创建触发器 创建触发器的CREATE TRIGGER语句的语法格式如下： CREATE TRIGGER trigger_name ON table_name | view_name [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [INSERT][,] [DELETE][,][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 ] }} 第 9 章 触 发 器
9.3创建触发器 1.创建 TINSERT触发器 【例9-1】为成绩表( Grade)创建一个 INSERT触发器,用 于自动计算学生已修的学分 第 USE Educational GO 9章触发 如果已存在同名触发器,则先进行删除操作 IF exists(SELECT FROM dbo. sysobjects Where id object id(N TRGrade Insert') and OB JECTPROPERTY (id, N IsTrigger')=1) DROP TRIGGER TRGrade Insert GO CREATE TRIGGER TRGrade Insert/米触发器名*/ ON Grade /*作用的表*/ FOR INSERT/*创建 INSERT触发器*/ AS ○
9.3 创建触发器 1．创建INSERT触发器 【例9-1】 为成绩表（Grade）创建一个INSERT触发器，用 于自动计算学生已修的学分。 USE Educational GO -- 如果已存在同名触发器，则先进行删除操作 IF exists(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'TRGrade_Insert') and OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER TRGrade_Insert GO CREATE TRIGGER TRGrade_Insert /* 触发器名 */ ON Grade /* 作用的表 */ FOR INSERT /* 创建INSERT触发器 */ AS 第 9 章 触 发 器
9.3创建触发器 DECLARE @Credi tHour TINYINT /米学分*/ declare @Grade TINYINT /*成绩*/ seleCT @Grade=G Grade. @CreditHour=C Credi tHour FROM Grade G INNER JOIN Inserted I 第 ON G Studentid=I StudentId and G Course ID=I CourseID 9章触发 INNER J(0 IN Course C/*按 CourseID联接 Course*/ ON C Course ID= I Coursed IF (@Grade>=60) /米成绩大于等于60*/ BEGIN UPDATE Student/*在已修学分中加上插入课程的学分*/ SET CreditHour=CreditHour @Credi thour FROM Student S. Inserted I WHERE S Studentid= I studentiD END GO ○
9.3 创建触发器 DECLARE @CreditHour TINYINT /* 学分 */ DECLARE @Grade TINYINT /* 成绩 */ SELECT @Grade=G.Grade, @CreditHour=C.CreditHour FROM Grade G INNER JOIN Inserted I ON G.StudentID = I.StudentID and G.CourseID = I.CourseID INNER JOIN Course C /* 按CourseID联接Course */ ON C.CourseID = I.CourseID IF (@Grade>=60) /* 成绩大于等于60 */ BEGIN UPDATE Student/* 在已修学分中加上插入课程的学分 */ SET CreditHour=CreditHour + @CreditHour FROM Student S, Inserted I WHERE S.StudentID = I.StudentID END GO 第 9 章 触 发 器
9.3创建触发器 2.创建 DELETE触发器 【例9-2】为成绩表( Grade)创建一个 DELETE触发器,用于自动计算学 生已修的学分。 USE Educational 第 GO 9章触发 如果已存在同名触发器,则先进行删除操作 IF exists(SELECT FROM dbo. sysobjects Where id object id(N TRGrade Delete') and OB JECTPROPERTY (id, N IsTrigger')=1) DROP TRIGGER TRGrade Delete GO CREATE TRIGGER TRGrade Delete/*触发器名*/ ON Grade /*作用的表*/ FOR DELETE /*创建 DELETE触发器*/ AS ○
9.3 创建触发器 2．创建DELETE触发器 【例9-2】为成绩表（Grade）创建一个DELETE触发器，用于自动计算学 生已修的学分。 USE Educational GO -- 如果已存在同名触发器，则先进行删除操作 IF exists(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'TRGrade_Delete') and OBJECTPROPERTY(id, N'IsTrigger') = 1) DROP TRIGGER TRGrade_Delete GO CREATE TRIGGER TRGrade_Delete /* 触发器名 */ ON Grade /* 作用的表 */ FOR DELETE /* 创建DELETE触发器 */ AS 第 9 章 触 发 器