任务 2   触发器的创建与执行

1. 创建触发器

DML触发器和DDL触发器的创建都可以使用T-SQL语句来实现，但是两者的语法格式上略有区别。

语法格式如下：

　　CREATE TRIGGER [ schema_name . ]trigger_name 

　　ON { table | view } 

　　[ WITH <dml_trigger_option> [ ,...n ] ]

　　{ FOR | AFTER | INSTEAD OF } 

　　{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 

　　[ WITH APPEND ] 

　　[ NOT FOR REPLICATION ] 

　　AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }。

各参数说明如下：

schema_name：DML触发器所属架构的名称。DML触发器的作用域是为其创建该触发器的表或视图的架构。对于DDL触发器则无法指定schema_name。

trigger_name：触发器的名称。trigger_name必须遵循标识符规则，但 trigger_name不能以#或##开头。

table | view：对其执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。可以根据需要指定表或视图的完全限定名称。视图只能被INSTEAD OF 触发器引用。不能对局部或全局临时表定义DML触发器。

WITH ENCRYPTION：对 CREATE TRIGGER 语句的文本进行模糊处理。使用 WITH ENCRYPTION 可以防止将触发器作为SQL Server复制的一部分进行发布。

EXECUTE AS：指定用于执行该触发器的安全上下文。允许控制SQL Server实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。

FOR | AFTER：AFTER 指定DML触发器仅在触发SQL 语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。如果仅指定FOR关键字，则AFTER为默认值。不能对视图定义AFTER触发器。

INSTEAD OF：指定执行DML触发器而不是触发SQL 语句，因此，其优先级高于触发语句的操作。不能为DDL或登录触发器指定INSTEAD OF。对于表或视图，每个INSERT、UPDATE或DELETE语句最多可定义一个INSTEAD OF触发器。但是，可以为具有自己的INSTEAD OF触发器的多个视图定义视图。INSTEAD OF触发器不可以用于使用 WITH CHECK OPTION的可更新视图。如果将INSTEAD OF触发器添加到指定了WITH CHECK OPTION的可更新视图中，则SQL Server将引发错误。用户须用ALTER VIEW删除该选项后才能定义INSTEAD OF触发器。

{ [DELETE] [,] [INSERT] [,] [UPDATE]}：指定数据修改语句，这些语句可在DML触发器对此表或视图进行尝试时激活该触发器。必须至少指定

一个选项。在触发器定义中允许使用上述选项的任意顺序组合。

WITH APPEND：指定应该再添加一个现有类型的触发器。WITH APPEND不能与 INSTEAD OF 触发器一起使用。如果显式声明了AFTER触发器，则也不能使用该子句。

NOT FOR REPLICATION：指示当复制代理修改涉及到触发器的表时，不应执行触发器。有关详细信息，请参阅使用 NOT FOR REPLICATION 来控制约束、标识和触发器。

sql_statement：触发条件和操作。触发器条件指定其它标准，用于确定尝试的DML、DDL或logon事件是否导致执行触发器操作。触发器可以包含任意数量和种类的 T-SQL 语句，常常包含控制流语言。

常用格式：

CREATE TRIGGER 触发器名

ON 表名或视图名

[WITH ENCRYPTION]

{FOR|AFTER|INSTEAD OF}{[INSERT][,][UPDATE] [,][DELETE]}

[NOT FOR REPLICATION]

AS

[IF UPDATE(列名)[AND|OR] UPDATE(列名)]

SQL语句

在 DML触发器中不允许使用ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG等语句。

【例】创建INSERT触发器。对“学生成绩管理系统”的[教务数据库]，在成绩表上创建一个触发器，当每次插入成绩记录时，检查学号在学生表中是否存在、课程编号在课程表中是否存在，如果不存在，则不允许插入该条记录。若录入成功，则显示‘恭喜！录入成功!’

　　本例代码如下：

　　USE [教务数据库]

　　 GO

CREATE TRIGGER AfterInsert

ON  成绩表 AFTER  INSERT

AS

BEGIN

PRINT  '恭喜！录入成功!'

END

触发检验：

INSERT INTO [教务数据库].[dbo].[成绩表]

           ([学号],[课程号],[成绩],[任课教师号])

     VALUES  (201906003,3003,93,202002)

练习：学号改成一个不在的学号试试，如：202006003

【例】创建UPDATE触发器。对“学生成绩管理系统”的[教务数据库]，在学生表上创建一个UPDATE触发器，阻止对学生表中学号字段值的修改。

本例代码如下：

USE [教务数据库]

GO

CREATE TRIGGER PreventUpdate

ON 学生表 AFTER UPDATE

AS

BEGIN

IF UPDATE(学号)

BEGIN

PRINT '警告!!  学号字段值不能修改！'

ROLLBACK TRANSACTION

END

END　

【例】创建DELETE触发器。对“学生成绩管理系统”的[教务数据库]，在学生表上创建一个DELETE触发器，当从学生表中删除一条学生记录时，如果该学生有成绩记录，则不允许删除。

本例代码如下：

USE  [教务数据库]

GO

CREATE  TRIGGER  PreventDel

ON 学生表  AFTER  DELETE

AS

BEGIN

DECLARE @stuno char(10)

SELECT @stuno=学号

FROM deleted

IF  EXISTS(SELECT * FROM 成绩  WHERE 学号=@stuno)

BEGIN

PRINT '该学生存在成绩记录，不允许删除!'

ROLLBACK TRANSACTION

END

END

【练一练】创建触发器TR_PRODUCT,当修改PRODUCT表中商品信息时，能显示其修改前和修改后的数据行。