第08章 存储过程和触发器

1、SQL Server 2005 SQL Server 2005 数据库数据库 应用与开发应用与开发 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） n触发器（触发器（Trigger）是一种响应数据操作语）是一种响应数据操作语 言（言（DML）事件或数据定义语言（）事件或数据定义语言（DDL） 事件而执行的特殊类型的存储过程，是在事件而执行的特殊类型的存储过程，是在 用户对某一表中的数据进行用户对某一表中的数据进行U

2、PDATE、 INSERT和和DELETE操作时被触发执行的操作时被触发执行的 一段程序。一段程序。 n触发器有助于强制引用完整性，以便在添触发器有助于强制引用完整性，以便在添 加、更新或删除表中的行时保留表之间已加、更新或删除表中的行时保留表之间已 定义的关系。定义的关系。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.3.1 触发器的作用触发器的作用 触发器可以实现由主键和外键所不能保证的触发器可以实现由

3、主键和外键所不能保证的 复杂的参照完整性和数据一致性。复杂的参照完整性和数据一致性。 （1）触发器的主要作用。）触发器的主要作用。 n触发器可以对数据库进行级联修改。触发器可以对数据库进行级联修改。 n实现比实现比CHECK约束更为复杂的限制。约束更为复杂的限制。 n强制表的修改要合乎业务规则。强制表的修改要合乎业务规则。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.3.1 触发器的作用触发器的作用 （2）

4、使用触发器的优点。）使用触发器的优点。 n触发器可通过数据库中的相关表实现级联更改。触发器可通过数据库中的相关表实现级联更改。 n触发器可以强制比用触发器可以强制比用CHECK约束定义的约束约束定义的约束 更为复杂的约束。更为复杂的约束。 n触发器也可以监测数据修改前后的表状态，并触发器也可以监测数据修改前后的表状态，并 根据其差异采取对策。根据其差异采取对策。 n一个表中的多个同类触发器（一个表中的多个同类触发器（INSERT、 UPDATE 或或 DELETE）允许采取多个不同的）允许采取多个不同的 对策以响应同一个修改语句。对策以响应同一个修改语句。 清华大学出版社清华大学出版社清华大学

5、出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.3.2 触发器的分类触发器的分类 两种类型的触发器：两种类型的触发器：DML触发器和触发器和DDL触发器。触发器。 1. DML触发器触发器 nDML触发器是在执行触发器是在执行INSERT、UPDATE或或DELETE语语 句时被激活的触发器。句时被激活的触发器。 nDML触发器又分为触发器又分为AFTER和和INSTEAD OF两种触发器两种触发器: nAFTER 触发器是在激活它的语句执行成功

6、后才激活触发器是在激活它的语句执行成功后才激活. nINSTEAD OF触发器的激活将替代相应的触发语句。触发器的激活将替代相应的触发语句。 n当数据库中发生数据操作语言（当数据库中发生数据操作语言（DML）事件时将调用）事件时将调用 DML 触发器。触发器。DML 触发器可以查询其他表，还可以包触发器可以查询其他表，还可以包 含复杂的含复杂的 Transact-SQL 语句。将触发器和触发它的语句语句。将触发器和触发它的语句 作为可在触发器内回滚的单个事务对待。如果检测到错作为可在触发器内回滚的单个事务对待。如果检测到错 误误,则整个事务即自动回滚。则整个事务即自动回滚。 清华大学出版社清华

7、大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.3.2 触发器的分类触发器的分类。 1. DML触发器触发器: DML 触发器通常用于以下场合：触发器通常用于以下场合： nDML 触发器可通过数据库中的相关表实现级联更改。触发器可通过数据库中的相关表实现级联更改。 nDML 触发器可以防止恶意或错误的触发器可以防止恶意或错误的 INSERT、UPDATE 以及以及 DELETE 操作，并强制执行比操作，并强制执行比 CHECK

8、约束定义的约束定义的 限制更为复杂的其他限制。限制更为复杂的其他限制。 n与与 CHECK 约束不同，约束不同，DML 触发器可引用其他表中的列。触发器可引用其他表中的列。 nDML 触发器可以评估数据修改前后表的状态，并根据该差触发器可以评估数据修改前后表的状态，并根据该差 异采取措施。异采取措施。 n一个表中的多个同类一个表中的多个同类DML触发器允许采取多个不同的操作触发器允许采取多个不同的操作 来响应同一个修改语句。来响应同一个修改语句。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据

9、库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.3.2 触发器的分类触发器的分类。 2. DDL触发器触发器 DDL触发器是在执行触发器是在执行CREATE、ALTER和和DROP语语 句时被激活的触发器，是由数据定义语言引起的。句时被激活的触发器，是由数据定义语言引起的。 如果要执行以下操作，可以使用如果要执行以下操作，可以使用DDL触发器：触发器： n要防止对数据库架构进行某些更改。要防止对数据库架构进行某些更改。 n希望数据库中发生某种情况以响应数据库架构希望数据库中发生某种情况以响应数据库架构 中的更改。中的更改。 n要记录数据库架构中的更改或事件

10、。要记录数据库架构中的更改或事件。 n仅在运行触发仅在运行触发 DDL 触发器的触发器的 DDL 语句后，语句后， DDL 触发器才会激发。触发器才会激发。DDL 触发器无法作为触发器无法作为 INSTEAD OF 触发器使用。触发器使用。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.3.3 触发器的工作原理触发器的工作原理 n在触发器的执行过程中，在触发器的执行过程中，SQL Server为每个触发器创建

11、和为每个触发器创建和 管理两个特殊的表，一个是插入表管理两个特殊的表，一个是插入表inserted表，一个是删除表，一个是删除 表表deleted表。这两个表建在数据库服务器的内存中，与触表。这两个表建在数据库服务器的内存中，与触 发器所在数据表的结构是完全一致。发器所在数据表的结构是完全一致。 n当由当由INSERT或或UPDATE语句激活相应触发器之后，所有语句激活相应触发器之后，所有 被添加或被更新的记录都被存储到被添加或被更新的记录都被存储到inserted表。当由表。当由 DELETE或或UPDATE语句激活相应触发器之后，所有被删语句激活相应触发器之后，所有被删 除的记录都被送到除

12、的记录都被送到deleted表。表。 n在触发器的执行过程中，可以读取这两个表中的内容，但在触发器的执行过程中，可以读取这两个表中的内容，但 不能修改它们。当触发器的工作完成之后，这两个表也将不能修改它们。当触发器的工作完成之后，这两个表也将 从内存中删除。从内存中删除。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.3.4 创建触发器前应该注意的问题创建触发器前应该注意的问题 在创建触发器前，需要注意以下一

13、些问题：在创建触发器前，需要注意以下一些问题： nCREATE TRIGGER 语句必须是批处理中的第一个语句，语句必须是批处理中的第一个语句， 而且只能用于一个表或视图。而且只能用于一个表或视图。 n创建触发器的权限默认分配给表的所有者，且不能将该权创建触发器的权限默认分配给表的所有者，且不能将该权 限转给其他用户。限转给其他用户。 n触发器可以引用当前数据库以外的对象，但只能在当前数触发器可以引用当前数据库以外的对象，但只能在当前数 据库中创建触发器。据库中创建触发器。 n不能在临时表或系统表上创建触发器，但是触发器可以引不能在临时表或系统表上创建触发器，但是触发器可以引 用临时表。不应引

14、用系统表，而应使用信息架构视图。用临时表。不应引用系统表，而应使用信息架构视图。 n在含有用在含有用 DELETE或或UPDATE操作定义的外键的表中，不操作定义的外键的表中，不 能定义能定义INSTEAD OF和和 INSTEAD OF UPDATE触发器。触发器。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.1 创建触发器创建触发器 创建触发器可以通过创建触发器可以通过SQL Server Mana

15、gement Studio和和CREATE TRIGGER语句两种方法。语句两种方法。 创建触发器时需要指定如下的选项：创建触发器时需要指定如下的选项： n触发器名称和需要定义触发器的表。触发器名称和需要定义触发器的表。 n触发器将何时激发。触发器将何时激发。 n激活触发器的数据修改语句。有效选项为激活触发器的数据修改语句。有效选项为 INSERT、UPDATE或或 DELETE。多个数据修。多个数据修 改语句可激活同一个触发器。改语句可激活同一个触发器。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2

16、005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.1 创建触发器创建触发器 1. 在在SQL Server Management Studio中创建触发器中创建触发器 （1）启动）启动SQL Server Management Studio，在，在 “对象资源管理器对象资源管理器”中展开中展开“数据数据 库库”|teaching|“表表” 。 （2）选择要创建触发器的表）选择要创建触发器的表student并展开。右单并展开。右单 “触发器触发器”节点，在弹出的快捷菜单中选择节点，在弹出的快捷菜单中选择“新新 建触发器建触发器”命令。命令。 （3

17、）此时弹出如图）此时弹出如图8.4所示的新建触发器编辑窗口，所示的新建触发器编辑窗口， 其中包含触发器模板，用户可以参照模板在其中其中包含触发器模板，用户可以参照模板在其中 输入触发器的输入触发器的Transact-SQL语句。语句。 （4）单击工具栏中的）单击工具栏中的“！”执行按钮，将触发器执行按钮，将触发器 保存到数据库中。保存到数据库中。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.1 创建触发

18、器创建触发器 2. 使用使用CREATE TRIGGER语句创建触发器语句创建触发器 创建触发器的语法格式如下：创建触发器的语法格式如下： CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statament ,n 例例8.8 为为student表创建一个触发器，用来禁止表创建一个触发器，用来禁止 更新学号字段的值。更新学号字段的值。 程序代码如下程序代码如下: CREATE TRIGGER update_s_t

19、r ON student AFTER UPDATE AS IF UPDATE(studentno) BEGIN RAISERROR(不能修改学号不能修改学号,16,2) ROLLBACK END 此时，若有更新语句如下：此时，若有更新语句如下： UPDATE student SET studentno=0837221508 WHERE studentno=0937221508 则提示则提示“不能修改学号不能修改学号”， 更新语句得不更新语句得不 到执行。到执行。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Serve

20、r 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.1 创建触发器创建触发器 例例8.9 为为course表创建一个触发器，用来防止用户删除任何表创建一个触发器，用来防止用户删除任何 必修课程的课程记录。必修课程的课程记录。 程序代码如下程序代码如下: CREATE TRIGGER delete_c_tr ON course INSTEAD OF DELETE AS IF EXISTS ( SELECT * FROM course WHERE type=必修必修 ) BEGIN RAISERROR(不能删除必修课程不能删除必修课程,16,2

21、) ROLLBACK END 此时，若删除新语句如下：此时，若删除新语句如下： DELETE FORM course WHERE type=必修必修 则提示则提示“不能删除必修课程不能删除必修课程”，删除语句得不到执，删除语句得不到执 行。行。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.1 创建触发器创建触发器 例例8.10 为为score表创建一个触发器，用来防止用户对表创建一个触发器，用来防止用户

22、对score表表 中的数据进行任何修改。中的数据进行任何修改。 程序代码如下程序代码如下: CREATE TRIGGER update_sc_tr ON score INSTEAD OF UPDATE AS RAISERROR(不能修改成绩表中的数据不能修改成绩表中的数据,16,2) 此时，若有更新语句如下此时，若有更新语句如下： UPDATE score SET final=60 则提示则提示“不能修改成绩表中的数据不能修改成绩表中的数据”，更新语句得不到执，更新语句得不到执 行。行。同一个触发器。同一个触发器。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005.

23、 SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.2 修改触发器修改触发器 1. 使用使用SQL Server Management Studio修改触发器修改触发器 修改触发器的操作步骤如下。修改触发器的操作步骤如下。 （1）在）在“对象资源管理器对象资源管理器”中展开中展开“数据库数据库”。 （2）选择触发器所在的数据库，如）选择触发器所在的数据库，如teaching数据库，并展数据库，并展 开该数据库的开该数据库的“表表”节点。节点。 （3）选择触发器所在的表）选择触发器所在的表

24、score，展开表中的，展开表中的“触发器触发器”。 （4）右击要修改的触发器，在弹出的快捷菜单中选择）右击要修改的触发器，在弹出的快捷菜单中选择“修修 改改”命令。命令。 （5）在弹出的触发器编辑窗口，用户可以直接进行修改。）在弹出的触发器编辑窗口，用户可以直接进行修改。 修改完毕，单击工具栏中的修改完毕，单击工具栏中的“！”按钮执行该触发器，按钮执行该触发器， 将修改后的触发器保存到数据库中。将修改后的触发器保存到数据库中。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库

25、应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.2 修改触发器修改触发器 2. 使用使用ALTER TRIGGER语句修改触发器语句修改触发器 ALTER TRIGGER语句的语法格式如下：语句的语法格式如下： ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFINSERT, UPDATE , DELETE AS sql_statament ,n 例例8.11 使用使用ALTER TRIGGER语句修改触发器语句修改触发器 update_s_tr，用来禁止

26、更新学号字段和姓名字段的值。，用来禁止更新学号字段和姓名字段的值。 程序代码如下程序代码如下: ALTER TRIGGER update_s_tr ON student AFTER UPDATE AS IF UPDATE(studentno) OR UPDATE(sname) BEGIN RAISERROR(不能修改学号或姓名不能修改学号或姓名,16,2) ROLLBACK END 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2

27、2版）版）版） 8.4.3 删除触发器删除触发器 1. 使用使用SQL Server Management Studio删除触发器删除触发器 操作步骤与修改相近。只是在右击触发器时，在弹操作步骤与修改相近。只是在右击触发器时，在弹 出的快捷菜单中选择出的快捷菜单中选择“删除删除”命令，单击命令，单击“确定确定” 按钮，即可删除该触发器。按钮，即可删除该触发器。 2. 使用使用DROP TRIGGER语句删除触发器语句删除触发器 DROP TRIGGER的语法格式如下：的语法格式如下： DROP TRIGGER trigger_name ,n 例如可以使用如下语句来删除触发器例如可以使用如下语句

28、来删除触发器update_s_tr。 DROP TRIGGER update_s_tr 。 清华大学出版社清华大学出版社清华大学出版社. SQL Server 2005. SQL Server 2005. SQL Server 2005数据库应用与开发数据库应用与开发数据库应用与开发 （第（第（第2 2 2版）版）版） 8.4.4 禁用触发器禁用触发器 1. 使用使用SQL Server Management Studio禁用触发器禁用触发器 操作步骤如下。操作步骤如下。 （1）在）在“对象资源管理器对象资源管理器”中展开中展开“数据库数据库”节点。节点。 （2）选择触发器所在的数据库，如）选择触发器所在的数据库，如teaching数据库，数据库， 并展开该数据库的并展开该数据库的“表表”节点。节点。 （3）选择触发器所在的表）选择触发器所在的表score，展开，展开“触发器触发器”节节 点。点。 （4）右击要禁用的触发器，在弹出的快捷菜单中选）右击要禁用的触发器，在弹出的快捷菜单中选 择择“禁用禁用”命令，弹出如图命令，弹出如图8.