第10章 触发器和游标

2013-9-10
SQL Server 2005
13
AFTER触发器示例
例10.1 修改学生表中的数据时，下述触发器将向客户 显示一条消息。 CREATE TRIGGER 学生_update ON 学生表 AFTER UPDATE AS BEGIN RAISERROR ('注意：有人修改学生表的数据',16,10) END GO
2013-9-10 SQL Server 2005 12
用SQL语句创建触发器
主要参数说明： trigger_name：触发器的名称,必须遵循标识符规 schema_name：触发器所属架构的名称。 table|view：指定触发器所在的数据表或视图。 WITH ENCRYPTION：对CREATE TRIGGER语句的文本 进行加密。 EXECUTE AS：用于执行该触发器的安全上下文。 AFTER：指定DML触发器仅在触发SQL语句中指定的所 有操作都已成功执行时才被激发。 {[DELETE][,][INSERT][,][UPDATE]}：指定数据修改语 句，这些语句可在DML触发器对此表或视图进行尝试时 激活该触发器。必须至少指定一个选项。WITH APPEND：指定应该再添加一个现有类型的触发器。
第10章 触发器和游标
10.1 触发器概述
10.1.1触发器的概念 触发器实际上就是一种特殊类型的存储过 程，它在一定操作时自动触发执行。在SQL Server 2005之前的版本中，触发器是针对数据 表的特殊的存储过程，当这个表发生了Insert、 Update或Delete操作时，如果该表有对应操作 的触发器，这个触发器就会自动激活执行。在 SQL Server 2005中，触发器有了更进一步的功 能，在数据表（库）发生Create、Alter和Drop 操作时，也会自动激活执行。
2013-9-10 SQL Server 2005 5
10.2.2 DML触发器的工作原理
After触发器的工作原理 After触发器是在记录更变完之后才被激 活执行。 Instead Of触发器的工作原理 Instead Of触发器与After触发器不同。 After触发器是在Insert、Update和Delete 操作完成后才激活的，而Instead Of触发 器，是在这些操作进行之前就激活了，并 且不再去执行原来的SQL操作，而去运行 触发器本身的SQL语句。
2013-9-10
SQL Server 2005
14
创建INSTEAD OF触发器
创建INSTEAD OF触发器的语法代码如下 ：
CREATE TRIGGER <trigge_name> ON [<schema name>.]<table or view name> [WITH ENCRYPTION|EXECUTE AS <CALLER|SELF|<user>>] {INSTEAD OF} {[INSERT][,][UPDATE]>[,]<[DELETE]} [WITH APPEND] [NOT FOR REPLICATION] AS <<sql statements>|EXTERNAL NAME <assembly method specifier>>
2013-9-10 SQL Server 2005 6
10.2.3 创建DML触发器的注意事项
CREATE TRIGGER语句必须是批处理中的第一 个 语句
创建DML触发器的权限默认分配给表的所有 者，且不能将该权限转给其他用户 DML触发器为数据库对象，其名称必须遵循 标识符的命名规则 只能在当前数据库中创建DML触发器 不能对临时表或系统表创建DML触发器 WRITETEXT语句不会触发INSERT或UPDATE 触发器
SQL Server 2005
4
10.2 DML触发器
10.2.1 DML触发器的类型 After触发器：这类触发器是在记录已经改变完 之后（after），才会被激活执行，它主要是用 于记录变更后的处理或检查，一旦发现错误， 也可以用Rollback Transaction语句来回滚本次 的操作。 Instead Of触发器：这类触发器一般是用来取 代原本要进行的操作，在记录变更之前发生的， 它并不去执行原来SQL语句里的操作（Insert、 Update、Delete），而去执行触发器本身所定 义的操作。
SQL Server 2005
11
用SQL语句创建触发器
创建AFTER触发器的语法代码如下： CREATE TRIGGER <trigge_name> ON [<schema name>.]<table or view name> [WITH ENCRYPTION|EXECUTE AS <CALLER|SELF|<user>>] {FOR|AFTER} {[INSERT][,][UPDATE]>[,]<[DELETE]} [WITH APPEND] [NOT FOR REPLICATION] AS <<sql statements>|EXTERNAL NAME <assembly method specifier>>
2.使用T-SQL语句禁用DML触发器
语法如下：
Alter table 数据表名 Disable trigger 触发器名或ALL 如果要禁用Βιβλιοθήκη Baidu有触发器，用“ALL”来代替触发器名。
2013-9-10 SQL Server 2005 21
启用DML触发器
1.图形界面方式下启用触发器 启用触发器与禁用触发器类似，只是在弹 出的快捷菜单中选择“启用”选项即可。 2.使用T-SQL语句启用触发器 其语法如下： Alter table 数据表名 Enable trigger 触发器名或ALL 如果要启用所有触发器，用“ALL”来代 替触发器名。
2013-9-10
SQL Server 2005
19
删除DML触发器
（1） 在图形界面方式下删除触发器 按照在SQL Server Management Studio中查看
触发器的方法，找到“触发器列表”对话框。 右击要删除的某个触发器，在弹出快捷菜单中
选择“删除”选项。
（2）用SQL语句删除触发器，删除触发器 的语法代码如下所示：
2013-9-10 SQL Server 2005 7
10.2.4 创建AFTER触发器
步骤如下： （1）启动“SQL Server Management Studio”，在 “对象资源管理器”下选择“数据库”，定位到具 体的数据库，展开其下的“表”树型目录，找到具 体的表，并选中其下的“触发器”项，如下图所示。
Drop Trigger 触发器名
2013-9-10
SQL Server 2005
20
禁用DML触发器
1．在图形界面方式下禁用DML触发器
按照在SQL Server Management Studio中查看触发器的 方法打开“触发器列表”对话框。 右击其中一个触发器，在弹出快捷菜单中选择“禁用” 选项，即可。
2013-9-10 SQL Server 2005 22
10.3 DDL触发器
DDL触发器是SQL Server 2005新增的一个 触发器类型，像常规触发器一样，DDL 触发器将激发存储过程以响应事件。但与 DML触发器不同的是，它们不会为响应针 对表或视图的UPDATE、INSERT或 DELETE语句而激发。相反，它们会为响 应多种数据定义语言(DDL)语句而激发。 这些语句主要是以 CREATE、ALTER和 DROP开头的语句。DDL触发器可用于管 理任务，例如审核和控制数据库操作。
2013-9-10 SQL Server 2005 23
10.3.1 创建DDL触发器
创建DDL触发器的语法代码如下： CREATE TRIGGER <trigge_name> ON {ALL SERVER|DATABASE} [WITH <ddl_trigger_option>[,...n]] {FOR|AFTER}{event_type|event_group}[,...n] AS {sql_statement[;][...n]|EXTERNAL NAME<method specifier >[;]} <ddl_trigger_option>::= [ENCRYPTION] [EXECUTE AS Clause] <method_specifier>::= assembly_name.class_name.method_name
2013-9-10
SQL Server 2005
16
查看触发器
1.通过 SQL Server Management Studio
2013-9-10
SQL Server 2005
17
查看触发器
2.通过系统存储过程 （1）sp_help 系统存储过程sp_help可以了解如触发器名称、 类型、创建时间等基本信息，其语法格式为： sp_help '触发器名' 例如：sp_help '学生_insert' （2）sp_helptext 系统存储过程sp_helptext可以查看触发器的文本 信息，其语法格式为： sp_helptext '触发器名' 例如：sp_helptext '学生_insert'
2013-9-10
SQL Server 2005
8
在图形界面下创建触发器
2013-9-10
SQL Server 2005
9
在图形界面下创建触发器
（2）右击“触发器”，在弹出的快捷菜单中选择“新建触发器” 选项，弹出“查询编辑器”对话框，在“查询编辑器”的编辑 区里SQL Server已经预写入了一些建立触发器相关的SQL语句， 如下图所示。
创建Instead Of触发器与创建After触发器的语法几乎 一样，只是简单地把After改为Instead Of。
2013-9-10
SQL Server 2005
15
INSTEAD OF触发器示例
例10.2 当有人试图修改学生表中的数据，利用下述触发器可以跳过 修改数据的SQL语句（防止数据被修改），并向客户端显示一条消 息。 CREATE TRIGGER 学生_update ON 学生表 INSTEAD OF UPDATE AS BEGIN RAISERROR ('对不起，学生表的数据不允许修改',16,10) END GO
2013-9-10 SQL Server 2005 2
10.1.2触发器的功能
完成更复杂的数据约束 检查所做的SQL所作的操作是否允许 修改其它数据表里的数据 调用更多的存储过程 返回自定义的错误信息 更改原本要操作的SQL语句 防止数据表结构被更改或数据表被删除
2013-9-10
SQL Server 2005
3
10.1.3触发器的类型
DML触发器 DML触发器是当数据库服务器中发生数 据操作语言（Data Manipulation Language）事件时执行的存储过程
DDL触发器
DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过 程。
2013-9-10
2013-9-10 SQL Server 2005 18
修改触发器
修改触发器的语法代码如下：
ALTER TRIGGER <trigge_name> ON {table|view} [WITH ENCRYPTION|EXECUTE AS <CALLER|SELF|<user>>] {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE]>[,]<[DELETE]} [NOT FOR REPLICATION] AS <<sql statements>|EXTERNAL NAME <assembly method specifier>> 分析上述语法代码可以发现，修改触发器语法中所涉及到主要参数和 创建触发器的主要参数几乎一样，在此不再赘述。
2013-9-10
SQL Server 2005
10
在图形界面下创建触发器
（3）修改“查询编辑器”里的代码 （4）单击工具栏中的“分析”按钮，检查 一下是否语法有错，如果在“结果”对话 框中出现“命令已成功完成”，则表示语 法没有错误。 （5）语法检查无误后，单击“执行”按钮， 生成触发器。
2013-9-10