9.4.2 创建触发器

1. 用SQL Server Management Studio创建触发器

(1) 启动SQL Server Management Studio，在对象资源管理器中，连接到SQL Server数据库引擎实例，再展开该实例。

(2) 展开“数据库”，选择要创建触发器的数据，展开“表”，右键单击“触发器”，然后选择“新建触发器”，如图9-43所示。

(3) 在“查询”菜单上，选择“指定模板参数的值”，或者按下Ctrl+Shift+M以打开“指定模板参数的值”对话框，可在该对话框中输入作者、日期、说明等相关信息。

(4) 单击“确定”按钮，然后在“查询编辑器”中使用相关语句替换注释。

①若要验证语法是否有效，请在“查询”菜单上选择“分析”。如果返回错误消息，则将该语句与上述信息进行比较，视需要进行更正并重复此步骤。

②若要创建DML触发器，请在“查询”菜单上选择“执行”。该DML触发器可作为数据库中的对象创建。

③若要查看对象资源管理器中列出的DML触发器，请右键单击“触发器”，然后选择“刷新”。

【例9-29】创建一个触发器（enroll_ins）：当向成绩表（enroll）中添加数据时，如果添加的数据与学生表（student）中的数据不匹配（没有对应的学号），则删除此数据（可设变量@bh）。

解：选择enroll表，新建触发器，在对应的创建触发器的代码处输入相关内容，如图9-44所示。

2. 使用SQL语句创建DML触发器

基本语法格式如下：

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH ＜dml_trigger_option＞ [ ,…n ] ]

{ FOR | AFTER | INSTEAD OF }

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

[ NOT FOR REPLICATION ]

AS { sql_statement [ ; ] [ , …n ] | EXTERNAL NAME ＜method specifier [ ; ] ＞}

＜dml_trigger_option＞::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

＜method_specifier＞::=

assembly_name.class_name.method_name

参数说明：

(1) schema_name：DML触发器所属架构的名称。DML触发器的作用域是为其创建该触发器的表或视图的架构。不能为 DDL 或登录触发器指定 schema_name。

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

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

(4) ENCRYPTION：对CREATE TRIGGER语句的文本进行模糊处理。

使用ENCRYPTION可以防止将触发器作为SQL Server复制的一部分进行发布。不能为CLR触发器指定WITH ENCRYPTION。

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

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

(7) INSTEAD OF：指定执行DML触发器而不是触发SQL语句，因此，其优先级高于触发语句的操作。不能为DDL或登录触发器指定INSTEAD OF。

(8) { [INSERT] [,] [UPDATE] [,] [DELETE]}：指定数据修改语句，这些语句可在 DML触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。

(9) NOT FOR REPLICATION：当复制代理修改涉及触发器的表时，不应执行触发器。

(10) sql_statement：触发条件和操作。触发器条件用于指定其他标准，用于确定尝试的DML、DDL或logon事件是否导致执行触发器操作。尝试上述操作时，将执行T-SQL语句中指定的触发器操作。DML触发器使用deleted和inserted逻辑（概念）表。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在 deleted和inserted表中保存了可能会被用户更改的行的旧值或新值。

(11) ＜ method_specifier ＞：对于CLR触发器，指定程序集与触发器绑定的方法。

注意：在使用触发器时的限制如下。

①CREATE TRIGGER必须是批处理中的第一条语句，并且只能应用于一个表。

②触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。

③如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。

④在同一条CREATE TRIGGER语句中可以为多种用户操作（如INSERT和UPDATE）定义相同的触发器操作。

⑤如果一个表的外键包含对定义的 DELETE/UPDATE 操作的级联，则不能对表定义INSTEAD OF DELETE/UPDATE触发器。

⑥在触发器内可以指定任意的SET语句。选择的SET选项在触发器执行期间保持有效，然后恢复为原来的设置。

⑦虽然TRUNCATE TABLE语句实际上就是DELETE语句，但是它不会激活触发器，因为该操作不记录各个行删除的情况。然而，仅那些具有执行TRUNCATE TABLE语句的权限的用户才需要考虑是否无意中因为此方式而导致没有使用 DELETE 触发器。

⑧无论有日志记录还是无日志记录，WRITETEXT语句都不触发触发器。

⑨在DML触发器中不允许使用下列T-SQL语句。

1）AFTER触发器

执行DML触发器时，会产生两个临时表deleted和inserted。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在deleted和inserted表中保存了可能会被用户更改的行的旧值或新值。

(1) 插入inserted表中存放的是更新前的记录。

—对于插入记录操作来说，插入表中存放的是要插入的数据。

—对于更新记录操作来说，插入表中存放的是要更新的记录。

(2) 删除deleted表中存放的是更新后的记录。

—对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）。

—对于删除记录操作来说，删除表里存入的是被删除的旧记录。

例如，若要检索deleted表中的所有值，则使用：select * from deleted

①使用INSERT触发器。

INSERT触发器通常用于更新时间标记字段，或者验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。

【例 9-30】创建的触发器（enroll_ins），即创建一个 INSERT 触发器：当向成绩表（enroll）中添加数据时，如果添加的数据与学生表（student）中的数据不匹配（没有对应的学号），则将此数据删除（可设变量@bh）。

解：代码如下。

USE students

GO

IF OBJECT_ID (' enroll_ins ', 'TR') IS NOT NULL

DROP TRIGGER enroll_ins;

GO

CREATE TRIGGER enroll_ins

ON enroll

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @bh char(10)

SELECT @bh= Inserted.sno FROM inserted

If NOT EXISTS(SELECT sno FROM student WHERE student.sno=@bh)

DELETE enroll WHERE sno=@bh

print '没有该学生的记录，成绩信息添加失败！'

END

GO

然后插入数据测试触发器，输入一个student表中没有的学生信息（编号95002），语句如下：

insert into enroll values('95002','c1',80);

具体的执行结果如图9-45所示。

②使用DELETE触发器。

当触发 DELETE 触发器时，从受影响的表中删除的行将被放置到一个特殊的临时表deleted表中，它保留已被删除数据行的一个副本。deleted表还允许引用由初始化DELETE语句产生的日志数据。

使用DELETE触发器时，需要考虑以下事项和原则。

· 当某行被添加到deleted表中时，它就不再存在于数据库中，因此，deleted表和数据库表没有相同的行。

· 创建deleted表时，空间是从内存中分配的。deleted表总是被存储在调整缓存中。

· 为DELETE动作定义的触发器并不执行TRUNCATE TABLE语句，原因在于日志不记录TRUNCATE TABLE语句。

【例9-31】创建触发器（tr_del_s）：当删除学生表（student）中的记录时，自动删除成绩表（enroll）中对应学号的记录（可设变量@bh），编写程序。

解：程序如下。

USE students

GO

IF OBJECT_ID (' tr_del_stu ', 'TR') IS NOT NULL

DROP TRIGGER tr_del_stu;

GO

CREATE TRIGGER tr_del_stu

ON student

AFTER DELETE

AS

BEGIN

DECLARE @bh char(10)

SELECT @bh=deleted.sno FROM deleted

DELETE enroll WHERE sno=@bh

print '该学生的个人记录以及对应的成绩信息已全部删除！'

END

GO

接下来插入新的学生信息和成绩信息数据，测试触发器是否会执行。输入student表中没有的学生信息（编号95002)的个人信息，然后插入该生的c1课程的成绩。语句如下：

insert into student values('95002','张扬','男',21,'机电系','湖北');

insert into enroll values('95002','c1',80);

验证触发器，删除95002号学生的信息，语句如下：

delete from student where sno='95002';

具体的执行结果如图9-46所示，结果提示触发器中的信息。同时查看student表、enroll表，发现对应的95002号学生的个人信息和成绩信息已全部删除。

③使用UPDATE触发器。

更新触发器是当UPDATE语句在目标表上运行的时候，就调用更新触发器。就像任何其他触发器一样，当调用触发器时，运行被触发的SQL语句并且执行动作。

【例9-32】创建触发器（tr_grade_kc）：当插入或更新学生成绩时，触发器检查该课程是否为考查课，若是，则通过的成绩只能以60分计，未通过的只能以40分计，编写程序。

解：程序如下。

USE students

GO

IF OBJECT_ID (' tr_grade_kc ', 'TR') IS NOT NULL

DROP TRIGGER tr_grade_kc;

GO

CREATE TRIGGER tr_grade_kc

ON enroll

FOR INSERT,UPDATE

AS

BEGIN

DECLARE

@SCORE numeric(9,0),

@CTYPE nchar(10)

SELECT @SCORE=grade,@CTYPE=Ctype

FROM course,inserted

WHERE inserted.cno=course.cno

IF(@CTYPE='考查') AND (@SCORE＜＞60 AND @SCORE＜＞40)

BEGIN

RAISERROR('该课程为考查课，成绩以60或40计!',16,1)

ROLLBACK TRANSACTION

END

END

GO

接下来插入新的学生信息和成绩信息数据，测试触发器是否会执行。输入student表中没有的学生信息（编号95002）的个人信息，然后插入该生的c4（软件工程）课程的成绩，该课程为考查课。语句如下：

insert into student values('95002','张扬','男',21,'机电系','湖北');

insert into enroll values('95002','c4',80);

具体的执行结果如图9-47所示，结果提示触发器中的信息，同时查看student表、enroll表，发现95002号学生的个人信息已经录入，但是c4课程的成绩信息没有插入。

接下来修改已经存在的学生的成绩信息数据，测试触发器是否会执行。修改95001号学生胡峰的c4（软件工程）课程的成绩，该课程为考查课。语句如下：

update enroll set grade=70 where sno='95001' and cno='c4';

具体的执行结果如图9-48所示。

如果把该生的该课程的成绩改成60分，则可以正常录入，执行结果如图9-49所示。语句如下：

update enroll set grade=60 where sno='95001' and cno='c4';

2）INSTEAD OF触发器

INSTEAD OF触发器用于代替通常的触发操作（AFTER触发器），该触发器表示并不执行其定义的操作（INSERT、UPDATE、DELETE），而只是执行触发器本身的内容。SQL Server 2012中支持带有一个或多个基本表的视图定义INSTEAD OF触发器，这些触发器可以扩展视图可支持的更新类型。

INSTEAD OF 触发器的主要优点是：使不可被修改的视图能够支持修改，其中典型的例子是分割视图。为了提高查询性能，分割视图通常是一个来自多个表的结果集，但是也不支持视图更新。

需注意以下几点：

(1) 对于表或视图，每个INSERT、UPDATE或DELETE语句最多可定义一个INSTEAD OF触发器。但是，可以为具有自己的INSTEAD OF触发器的多个视图定义视图。

(2) INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。如果将INSTEAD OF触发器添加到指定了WITH CHECK OPTION的可更新视图中，则SQL Server将引发错误。用户须用ALTER VIEW删除该选项后才能定义INSTEAD OF触发器。

(3) 对于INSTEAD OF触发器，不允许对具有指定级联操作ON DELETE的引用关系的表使用DELETE选项。同样，也不允许对具有指定级联操作ON UPDATE的引用关系的表使用UPDATE选项。

【例9-33】创建触发器sex_control：当插入或更新学生基本资料时，该触发器检查指定插入记录的性别是否只是男或女。若是，则插入数据；若不是，则给出错误信息并回滚。

解：程序如下。

CREATE TRIGGER sex_control

ON student

INSTEAD Of INSERT

AS

DECLARE

@sno char(10), @sname char(10), @ssex char(2),

@sage numeric(9,0), @sdept char(10), @sbplace char(10)

--inserted为临时表，查询被修改的记录的性别

SELECT @sno=sno,@sname=sname,@ssex=sex,

@sage=age, @sdept=department,@sbplace=bplace

FROM inserted

IF NOT((rtrim(@ssex)='男') OR (rtrim(@ssex)='女'))

BEGIN

RAISERROR('性别只能是男或者女!不能是%s',16,1,@ssex)

ROLLBACK --撤销所做的更改

END

ELSE

INSERT INTO student VALUES(@sno,@sname,@ssex,@sage,@sdept,@sbplace )

创建好了触发器后，插入一条数据进行测试，语句如下：

INSERT INTO student VALUES('95008','李伟','南',21,'机电系','湖北 ')

运行结果显示，插入不成功，并给出提示，如图9-50所示。

接下来插入一条正确的数据：

INSERT INTO student VALUES('95008','李伟','男',21,'机电系','湖北')

提示插入成功，一行受影响，再去查看学生表，发现该学生信息已经输入，结果如图9-51所示。

3. 使用SQL语句创建DDL触发器

使用CREATE TRIGGER语句创建DDL触发器的语法格式如下：

CREATE TRIGGER trigger_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 ]

参数说明如下（部分参数和DML触发器的说明一样，不再重复）。

(1) ALL SERVER：将DDL或登录触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现event_type或event_group，就会激发该触发器。

(2) DATABASE：将 DDL 触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现event_type或event_group，就会激发该触发器。

(3) event_type：执行之后将导致激发DDL触发器的T-SQL语言事件的名称。

(4) event_group：预定义T-SQL语言事件分组的名称。

执行任何属于event_group的T-SQL语言事件之后，都将激发DDL触发器。DDL事件组中列出了DDL触发器的有效事件组。

【例9-34】创建一个DDL触发器：保护当前SQL Server服务器里的所有数据库不能被删除。

解：具体代码如下。

CREATE TRIGGER drop_database_error

ON all server

FOR DROP_DATABASE

AS

PRINT '对不起，您不能删除数据库'

ROLLBACK ;

GO

触发器创建成功后，尝试删除系统中的customer数据库，提示错误结果如图9-52所示。