SQL 第12章 触发器

1、第第12章章 触发器的创建和使用触发器的创建和使用 12. 1 概述概述12. 2 创建触发器创建触发器12. 3 触发器实施数据完整性实例触发器实施数据完整性实例12. 4 查看、修改和删除触发器查看、修改和删除触发器12. 5 使用触发器的注意事项使用触发器的注意事项返回目录返回目录12. 1 概述 12. 1. 1 触发器的概念及分类触发器的概念及分类 触发器是一种特殊类型的存储过程，不能用名称调用。 由事件触发，在表中数据发生变化时自动强制执行。触发器也有三种类型：触发器也有三种类型：INSERT、UPDATE、DELETE。 必须与表紧密相连，完成难以用普通约束实现的复杂功能。 触发

2、器是一个功能强大的工具。防止对数据进行不正确、未授权或不一致的修改。触发器与触发它的语句可看作同一个事务的不同部分。12.1.2 触发器的工作原理触发器的工作原理 两个专用的临时表：inserted表或者deleted表。 inserted表和deleted表总是与被该触发器作用的表结构相同 只能由创建它们的触发器引用。 它们是临时的逻辑表，由系统来维护。 它们存放于内存中，并不存放在数据库中。 触发器工作完成后，与该触发器相关的这两个表也会被删除。 1. INSERT触发器的工作原理触发器的工作原理 表中插入数据事件-触发INSERT触发器-创建inserted表-，数据加到该触发器表和in

3、serted表执行INSERT触发器删除inserted表- INSERT触发器执行完毕. 2. DELETE触发器的工作原理触发器的工作原理 表中删除数据事件-触发DELETE触发器-创建deleted表（保存删除记录）-执行DELETE触发器触发器删除deleted表触发器执行完毕。 注意: 当被删除的记录放在deleted表中的时候，该记录就不会存在于数据库的表中了。因此，deleted表和数据库表之间没有共同的记录。 3. UPDATE触发器的工作原理触发器的工作原理 修改一条记录就等于删除一条旧记录,插入一条新记录。 UPDATE触发器自动触发执行，相应的更新触发器创建一个delet

4、ed表和inserted表，表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。 触发器可以检查inserted表和deleted表以及被修改的表，以确定是否修改了数据行和应该如何执行触发器的操作。12.2 触发器的创建12.2.1 使用使用T-SQL语句创建触发器语句创建触发器 1. 创建触发器的创建触发器的SQL语句语法：语句语法： 创建触发器的一般语法如下。CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE,INSERT,UPDATEASsql_statement trig

5、ger_name ：要创建的触发器名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。 table_name ：指定所创建的触发器与之相关联的表名。必须是一个现存的表。 WITH ENCRYPTION ：加密创建触发器的文本。 FOR INSERT ,DELETE ,UPDATE ：指定所创建的触发器将在发生哪些事件时被触发，也即指定创建触发器的类型。“INSERT”，表示创建插入触发器；“DELETE”，表示创建删除触发器；“UPDATE” 表示创建更新触发器。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，以逗号分隔这些选项。 sql

6、_statement ：指定触发器执行的SQL语句。 注意: 触发器只能在当前数据库中创建. 并且一个触发器只能作用在一个表上。 在同一条 CREATE TRIGGER 语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触发器操作。 可以对视图建立触发器，只要将视图名称作为table_name用在创建语法中就可以了。但是，对视图建立触发器有一些限制. 2. 创建创建INSERT触发器触发器 下面以为表student建立一个插入触发器为例，介绍创建INSERT触发器的方法。 当某个班级增加一名学生，即向表student中插入一行数据时，需要更改该学生所在班级的记录，以增加该班

7、级的学生总人数。下例使用INSERT触发器自动完成这个工作。【例【例12-1】为student表建立INSERT触发器以自动更新class表学生人数。USE jwglGO/* 如果存在同名的触发器，则删除之*/IF EXISTS ( SELECT name FROM sysobjects WHERE type =TR AND name =student_insert)DROP TRIGGER student_insertGO CREATE TRIGGER student_insert ON student FOR INSERTASDECLARE NumOfStudent TINYINTSELE

8、CT NumOfStudent = c.student_num FROM class c ,inserted i WHERE c.class_id = i.class_idIF (NumOfStudent 0) BEGIN UPDATE class SET student_num = student_num + 1 FROM class c ,inserted i WHERE c.class_id = i.class_id ENDELSE (空值，直接写下面代码也可空值，直接写下面代码也可)BEGIN UPDATE class SET student_num = ( SELECT COUNT(

9、s.student_id) FROM student s ,inserted i WHERE s.class_id = i.class_id) FROM class c, inserted i WHERE c.class_id = i.class_id ENDGO 下面来测试它的运行情况。 .首先查询一个班级的当前人数首先查询一个班级的当前人数，如“g99403”。执行如下代码： SELECT * FROM class WHERE class_id = g99403 系统显示如下运行结果：class_id monitor classroom student_num g99403 成佳洱 教学楼

10、212 5 从上面执行结果可以看见，当前g99403班级当前的学生人数是5人。 .现在，使用上一章建立的添加学生记录的存储过程现在，使用上一章建立的添加学生记录的存储过程spAddStudent添加一个名字为程涛的学生记录添加一个名字为程涛的学生记录。执行如下代码： EXEC spAddStudent g9940306,程涛,男,1981-9-22,g99403,1999-9-1,南京市御道街2号 .再一次使用前面的语句查询再一次使用前面的语句查询g99403班级的信息，可以看班级的信息，可以看到下面的结果显示到下面的结果显示：class_id monitor classroom studen

11、t_num g99403 成佳洱 教学楼212 6 上面执行结果显示，class表中该班级记录的student_num字段已经自动更新为6了。 3. 创建创建DELETE触发器触发器 当从某个班级删除一名学生，即从表student中删除一行数据时，需要更改该学生所在班级的学生总人数。对student_insert的代码稍加修改就可以得到能自动完成这个工作的删除触发器。【例【例12-2】为student表建立DELETE触发器，在删除学生记录时自动更新class表中相应班级的学生人数。USE jwglGO/* 如果存在同名的触发器，则删除之*/IF EXISTS ( SELECT name FR

12、OM sysobjectsWHERE type = TR AND name = student_delete )DROP TRIGGER student_deleteGOCREATE TRIGGER student_delete ON studentFOR DELETEASDECLARE NumOfStudent TINYINTSELECT NumOfStudent = c.student_num FROM class c ,deleted d WHERE c.class_id = d.class_idIF (NumOfStudent 0) BEGIN UPDATE class SET stu

13、dent_num = student_num - 1 FROM class c ,deleted d WHERE c.class_id = d.class_id ENDELSE BEGIN UPDATE class SET student_num = ( SELECT COUNT(s.student_id) FROM student s ,deleted d WHERE s.class_id = d.class_id) FROM class c, deleted d WHERE c.class_id = d.class_id ENDGO 4. 创建创建UPDATE触发器触发器 在实际的教务管理

14、中，可能会有教师在各个院系之间进行工作调动，一方面需要更新teacher表中相关教师的department_id列的内容；另一方面，一个教师从一个系调动到另一个系，会影响两个系的教师总人数，这就需要更新这两个系的教师总人数。现在来为teacher表建立一个更新触发器，实现系信息表department中相关列的自动更新。【例【例12-3】为teacher表建立UPDATE触发器，在教师数据变更时自动更新department表的教师人数。USE jwglGO/* 如果存在同名的触发器，则删除之* /IF EXISTS ( SELECT name FROM sysobjectsWHERE type

15、= TR AND name = teacher_update )DROP TRIGGER teacher_updateGO/* 建立更新触发器 */CREATE TRIGGER teacher_update ON teacherFOR UPDATEASUPDATE department SET teacher_num = (SELECT COUNT(t.teacher_id) FROM teacher t,inserted iWHERE t.department_id = i.department_id )FROM department d , inserted i WHERE d.depar

16、tment_id = i.department_id (目的系)UPDATE department SET teacher_num = (SELECT COUNT ( t.teacher_id ) FROM teacher t , deleted eWHERE t.department_id = e.department_id )FROM department d , deleted eWHERE d.department_id = e.department_id (原来系)GO 下面我们来验证一下这个触发器的功能。 1. 首先看看当前department表中各系的数据，执行 SELECT *

17、 FROM department 语句，语句，系统显示的执行结果如下：department_id department_name department_header teacher_num dep_01 无线电无线电 王敬远王敬远 1dep_02 通信与信息工程通信与信息工程 康辉康辉 1dep_03 电子工程电子工程 董一平董一平 2dep_04 计算机科学计算机科学 纪云纪云 5 2. 然后我们修改一个教师的department_id，然后再查询department表的数据： UPDATE teacher SET department_id = dep_01 WHERE teacher_n

18、ame =潘惠潘惠 3. SELECT * FROM department 结果如下：department_id department_name department_header teacher_num dep_01 无线电无线电 王敬远王敬远 2dep_02 通信与信息工程通信与信息工程 康辉康辉 1dep_03 电子工程电子工程 董一平董一平 1dep_04 计算机科学计算机科学 纪云纪云 5 现在，由于一个教师从“电子工程”系调到了“无线电”系，“无线电”系的教师人数增加为2，而“电子工程”系的教师人数减少为1。这个UPDATE触发器的功能实现了。注意: 有时更新某些列时,才执行触发器

19、,更新teacher表的department_id列触发,跟新其他列时,不执行代码. IF UPDATE IF UPDATE (department_id)【例【例12-4】根据示例3，使用IF UPDATE进行优化，只对teacher表特定列的更新作出触发器操作。USE jwglGO/* 如果存在同名的触发器，则删除之* /IF EXISTS ( SELECT name FROM sysobjectsWHERE type = TR AND name = teacher_update )DROP TRIGGER teacher_updateGO/* 建立更新触发器 */CREATE TRIGG

20、ER teacher_update ON teacherFOR UPDATEAS/* 如果更新teacher表的department_id列，则执行对department表teacher_num列的更新* /IF UPDATE (department_id)BEGIN UPDATE department SET teacher_num = ( SELECT COUNT(t.teacher_id) FROM teacher t , inserted iWHERE t.department_id = i.department_id ) FROM department d , inserted i

21、WHERE d.department_id = i.department_id UPDATE department SET teacher_num = ( SELECT COUNT(t.teacher_id) FROM teacher t , deleted eWHERE t.department_id = e.department_id ) FROM department d , deleted e WHERE d.department_id = e.department_idENDGO12.2.2 使用企业管理器创建触发器使用企业管理器创建触发器 在企业管理器中使用以下步骤创建触发器： 展

22、开服务器组，然后展开服务器。 展开“数据库”文件夹，展开含触发器的表所属的数据库（例如：教务管理数据库JWGL），然后单击“表”文件夹。 在详细信息窗格中，右击将在其上创建触发器的表（如student），指向“所有任务”菜单，然后单击“管理触发器”命令。如图12-2所示。 图12-2 触发器属性界面 在“名称”中，单击 。 在“文本”框中显示了创建触发器的关键T-SQL语句，输入和修改触发器的文本。 若要检查语法，单击“检查语法”命令。 点击“确定”完成创建，点击“关闭”关闭对话框。 创建触发器时，一般是在查询分析器中直接使用创建触发器的语法来定义触发器。本节后面的内容都是在查询分析器中完成的

23、。12.3 触发器实施数据完整性实例12.3.1 实现参照完整性实现参照完整性 触发器能够维持两个表间的参照完整性（Referential Integrity），就像外键一样。外键执行这个任务的效率更高，因为它们在数据改变之前被测试，而不像触发器在数据改变后才触发。但通过使用T-SQL代码，它可以包含复杂的处理逻辑，便于成功实现级联删除和更新。 下面一个例子说明了触发器如何实现参照完整性。【例【例12-5】向teacher_course_class表添加一行数据，检查所插入数据的有效性。确保教师（teacher_id）存在teacher表中，课程（course_id）存在于course表中，而

24、班级（class_id）必须存在于class表中。使用下面的代码来定义teacher_course_class表INSERT触发器：CREATE TRIGGER tcc_insert ON teacher_course_classFOR INSERTASIF (NOT EXISTS ( SELECT teacher_id FROM teacher WHERE teacher_id IN ( SELECT teacher_id FROM inserted ) ) OR( NOT EXISTS ( SELECT course_id FROM course WHERE course_id IN (

25、SELECT course_id FROM inserted ) ) OR( NOT EXISTS ( SELECT class_id FROM class WHERE class_id IN ( SELECT class_id FROM inserted ) )BEGIN PRINT 添加记录操作不能完成！ PRINT 输入的教师编号、课程编号或班级编号有错误。 ROLLBACK TRANSACTIONEND每个条件的两个查询语句颠倒位置如何？ 当tcc_insert触发器被触发时，将检查teacher、course和class表，测试inserted临时表中的teacher_id、cour

26、se_id和class_id是否存在这三个表中。如果某一项不存在则撤消添加操作。12.3.2 实施特殊实施特殊业务规则业务规则 实现一些特殊业务规则是触发器运用的一个重要方面。例如，如果一个教师正在为某些班级的课程授课，则不能删除这个教师的数据。【例【例12-6】定义teacher表的DELETE触发器，实施“正在授课的教师数据不能删除”这个业务规则：CREATE TRIGGER teacher_delete ON teacherFOR DELETEASIF ( EXISTS ( SELECT teacher_id FROM teacher_course_class WHERE teacher

27、_id IN ( SELECT teacher_id FROM deleted )BEGIN PRINT 删除记录操作不能完成！ PRINT 该教师正在为一些班级授课。 ROLLBACK TRANSACTIONENDGO12.4 查看、修改和删除触发器12.4.1 查看触发器信息查看触发器信息 1. 使用使用T-SQL语句查看触发器信息语句查看触发器信息 查看表中的触发器信息 EXEC sp_helptrigger student 查看触发器定义查看触发器定义 EXEC sp_helptext student_insert 查看触发器的相关性查看触发器的相关性 EXEC sp_depends

28、student_delete 2. 使用企业管理器查看触发器相关信息使用企业管理器查看触发器相关信息 使用企业管理器查看表中的触发器信息及触发器的定义文本 展开服务器组，然后展开服务器。 展开“数据库”文件夹，展开含触发器的表所属的数据库，然后单击“表”文件夹。 在详细信息窗格中，右击触发器所在的表，指向“所有任务”菜单，然后单击“管理触发器”命令。 展开“名称”下拉框即可查看该表上的触发器，选择一触发器即可在“文本”区显示该触发器的定义文本。 使用企业管理器查看的相关性 在企业管理器中，使用以下步骤来查看触发器的相关性： 展开服务器组，然后展开服务器。 展开“数据库”文件夹，展开触发器所属的

29、数据库，然后单击“表”文件夹。 在详细信息窗格中，右击触发器所属的表，指向“所有任务”菜单，然后单击“显示相关性”命令。 在“对象”中，单击要查看其相关性的触发器。 12.4.2 修改触发器修改触发器 1. 使用使用T-SQL语句修改触发器语句修改触发器 使用T-SQL语句ALTER TRIGGER可以修改触发器，它的语法与CREATE TRIGGER类似。具体语法形式如下：ALTER TRIGGER trigger_name ON tablename WITH ENCRYPTION FOR DELETE , INSERT , UPDATE ASsql_statement 在以上语法形式中：

30、trigger_name ： 要更改的触发器名称。 tablename ：指定触发器在其上执行的表或视图名字。 WITH ENCRYPTION ：加密触发器的定义成本。 FOR DELETE,INSERT,UPDATE ：指定所更改的触发器将在发生哪些事件时被触发 sql_statement ：指定触发器执行的SQL语句。 2. 使用企业管理器修改触发器使用企业管理器修改触发器 使用企业管理器对触发器进行修改的具体步骤如下： 展开服务器组，然后展开服务器。 展开“数据库”文件夹，展开含触发器的表所属的数据库，然后单击“表”文件夹。 在详细信息窗格中，右击触发器所在的表，指向“所有任务”菜单，然

31、后单击“管理触发器”命令。 在“名称”框中选择触发器的名称。 按需要在“文本”字段中更改触发器的文本。 若要检查触发器的语法，单击“检查语法”命令。 点击“确定”按钮。12.4.3 删除触发器删除触发器 当不再需要某个触发器时，可将其删除。当触发器被删除时，它所基于的表和数据并不受影响。删除表将自动删除其上的所有触发器。 1. 使用使用T-SQL语句删除触发器语句删除触发器 使用DROP TRIGGER语句可以从当前数据库某个表中删除一个或多个触发器。其语法如下所示： DROP TRIGGER trigger ,.n 参数： trigger ： 要删除的触发器名称。 n ： 表示可以删除多个触发器的占位符。【例【例12-7】删除触发器student_delete，执行如下的语句。USE jwglIF EXISTS ( SELECT name FROM sysobjects WHERE name = student_del