第16章
SQL触发器
前面已经介绍过了表、视图、存储过程以及函数的创建。一般而言，创建这些对象后，需要配置一些对应的操作。例如，执行SELECT语句查询数据，执行EXEC命令执行存储过程等。SQL也支持自动执行的对象，对数据的更改作出反应，即触发器。
16.1触发器的基本概念
触发器是一种特殊的存储过程，它在表的数据变化时发生作用。触发器可以维护数据完整性。
16.1.1触发器简介
触发器在数据库里以独立的对象存储，与存储过程不同的是，存储过程通过其他程序来启动运行，而触发器是由一个事件来启动运行。即当某个事件发生时，触发器自动地隐式运行。并且，触发器不能接收参数。
触发器对象定义了触发器的特征和被调用时采取的行动。而这些动作是通过一个或多个SQL语句来实现的。SQL支持3种类型的触发器：INSERT（插入）、UPDATE（更新）和DELETE（删除）。当向表中插入数据、更新数据或删除数据时，触发器就被调用。通过给表定义一个或多个触发器，可以指定哪个数据修改时，可以激发触发器。
16.1.2触发器执行环境
触发器执行环境包含了触发器正确执行所必需的信息。这些信息主要是触发器本身的细节和触发器所定义的目标表。另外，触发器执行环境还包括一个或两个测试表，称之为INSERTED 表和DELETED表。测试表是虚表，用于保存目标表更新、插入或删除的数据信息。
这些测试表用来测试数据修改的结果，以及设置触发器行动的条件。用户不能直接修改测试表中的数据，但能在SELECT语句中，使用这些表来检测INSERT，UPDATE或DELETE 的结果。各种类型触发器用到的测试表如图16.1所示（针对SQL Server数据库系统而言）。
第16章SQL触发器
**
…
新数据INSERT
t r i g g e r表I n s e r e d表
**
…
被删除数据
DELETE
t r i g g e
r表D e l e t e d表
**
…
被删除数据
UPDATE
t r i g g e r表D e l e t e d表
新数据（修改
的数据）
I n s e r e d表
修改的数据
图16.1触发器测试表
deleted表存放了DELETE和UPDA TE语句中相关行的副本。在DELETE或UPDA TE
语句的执行中，这些相关行从trigger表中移到了deleted表中。一般情况下，这两张表
中无共同行。
insertded表存放了INSERT和UPDA TE语句中的副本。在INSERT或UPDA TE语句的
执行中，这些新行同时被加到inserted表和trigger表中。inserted表中的行是trigger表中
新行的副本。
一个UPDA TE效果上等价于一个DELETE再接着一个INSERT。首先“旧”行被复制
到deleted表中，然后新行被复制到trigger表和inserted表中。
16.2SQL Server中的触发器
当对某一表进行操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合规则。触发器的作用就是保证参照完整性和数据的一致性。
16.2.1SQL Server触发器的种类
SQL Server2000及其以后的版本支持两种类型的触发器：AFTER触发器和INSTEAD OF触发器。其中AFTER触发器即为前面介绍的UPDATE、INSERT、DELETE触发器。
INSTEAD OF触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF触发器，也可以在视图上定INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。
16.2.2使用CREATE TRIGGER命令创建触发器
在SQL Server中，可以采用CREATE TRIGGER命令创建触发器。语法如下。
CREATE TRIGGER trigger_name
ON{table|view}
[WITH ENCRYPTION]
{
{{FOR|AFTER|INSTEAD OF}{[DELETE][,][INSERT][,][UPDATE]}
326
327
16.2SQL Server 中的触发器
[NOT FOR
REPLICATION]
AS
[{IF UPDATE
(column)
[{AND|OR }UPDATE (column)]
[...n ]
|IF (COLUMNS_UPDATED (){bitwise_operator }updated_bitmask )
{
comparison_operator }column_bitmask [...n ]
}]
sql_statement [...n
]}}
说明如下。
trigger_name ：为用户要创建的触发器的名字，触发器的名字必须符合SQL Server 的命名规则，且其名字在当前数据库中必须是惟一的。
Table 、view ：与触发器相关联的表或视图的名字，并且该表或视图必须已经在数据库中存在。
WITH ENCRYPTION ：表示对含有CREATE TRIGGER 文本的syscomments 表进行加密，防止用户通过查询syscomments 表获取触发器的代码。
AFTER ：表示只有执行了指定的操作（INSERT
、DELETE 、或UPDATE ）之后，触发器才被激活，执行触发器中的SQL 语句。
FOR ：表示为AFTER 触发器，且该触发器仅能在表上创建。INSTEAD
OF ：指定触发器为INSTEAD OF 触发器。
说明每个表最多只能有一个INSTEAD
OF （INSERT
、UPDATE 、DELETE ）触发器。然而
可以为每个表创建多个视图，对每个视图都可以有不同的INSTEAD OF 触发器。有关INSTEAD OF 触发器的详细信息在16.2.6节会有详细介绍。
DELETE 、INSERT 、
UPDATE ：指明执行哪种操作，将激活触发器。至少要包含3种操作类型种的一种，也可以是3种操作语句的任意组合。其中三者的顺序不受限制，且各选项要用逗号隔开。
NOT FOR REPLICA TION ：告诉DBMS
，当复制表时，触发器不能被执行。AS ：后面列出触发器将要执行的动作。
IF UPDATE column ：用来测定对某一确定列是
INSERT 操作还是UPDA TE 操作。如果要测试INSERT 还是UPDA TE 操作的列多于一列，可用AND 或OR 逻辑连接向IF UPDA TE 子句添加所希望的附加列名。
IF COLUMNS_UPDA TED()：仅在INSERT 和UPDA TE 类型的触发器中使用，检查列是被更新还是被插入。
bitwise_operator ：代表位逻辑运算符，常用“&”。
updated_bitmask ：表示列的整位掩码。其中最右边的位表示表或视图的第1列，左边第2位代表第2列，依此类推。
comparison_operator ：表示比较操作符。可以是“=”或者“>”。“=”表示检查在updated_bitmask 中定义的所有列是否都被更新，用“>”表示检查是否在updated_bitmask
328
第16章
SQL 触发器
中定义的某些列被更新。
column_bitmask ：在IF COLUMNS_UPDA TED()子句中，要测试的是否被更新的列的序号的掩码。
说明
为了便于理解，这里给出一个使用IF COLUMNS_UPDA TED()子句的例子。如果表T 包括C1、C2、C3、C4、C5和C66列，为了检查C2、C4或者C6列是否更新过，可使用42（二进制表示为“101010”）作为掩码，表示为：IF （COLUMNS_UPDATED()&42）>0；如果检查C2、C4和C63列是否都被更新过，表示为：IF （COLUMNS_UPDATED()&42
）=42。
sql_statement ：代表包含在触发器中的处理语句。
当不再需要触发器时，可用DROP TRIGGER 语句删除触发器。语法如下。
DROP TRIGGER trigger_name [...n ]
16.2.3INSERT 触发器
实例1
创建INSERT 触发器
为STUDENT 表创建触发器S_insert ，当向STUDENT 表中插入数据时，要求学号必须以“97”开头，且课程号CNO 必须在COURSE 表中，否则取消插入操作。实例代码如下。
CREATE TRIGGER S_insert ON STUDENT FOR INSERT AS
DECLARE @S_no VARCHAR(4),@S_cno INT SELECT @S_no=SNO,@S_cno=CNO FROM INSERTED
IF (LEFT(@S_no,2)!='97')BEGIN
ROLLBACK TRANSACTION
RAISERROR('输入的学号：%s 不是97级的学生，请确认后重新录入！',16,1,@S_no)END
IF(@S_cno NOT IN (SELECT CNO FROM COURSE))BEGIN
ROLLBACK TRANSACTION
RAISERROR('输入的课程号：%d 在COURSE 表中不存在，请确认后重新录入！',16,1,@S_cno)END
当通过如下语句向STUDENT 表中插入数据时：
INSERT INTO STUDENT VALUES('9602','王永','机械工程','男',2,76,'必修')
由于插入数据的学号为“9602”，并不是以“97”开头，所以执行S_insert 触发器时，将执行“ROLLBACK TRANSACTION ”语句，取消完成的工作，并执行RAISERROR 语句给出错误信息。
运行结果如下。
输入的学号：9602不是97级的学生，请确认后重新录入！
当通过如下语句向STUDENT表中插入数据时：
INSERT INTO STUDENT VALUES('9702','王永','机械工程','男',12,76,'必修')
由于课程号12在COURSE表中不存在，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消工作，并执行RAISERROR语句给出错误信息。运行结果：
输入的课程号：12在COURSE表中不存在，请确认后重新录入！
可以在一个表上创建多个触发器，数据库把一个表中所有触发器都看作同一事务的一部分。因此只要其中一个触发器执行了ROLLBACK TRAN SACTION语句，那么所有的操作（与该INSERT语句有关）都将被取消。
16.2.4DELETE触发器
实例2创建DELETE触发器
为STUDENT表创建DELETE触发器S_delete，当一次删除的记录大于一行或者删除记录的课程为本系教师所开设时，取消删除操作。实例代码：
CREATE TRIGGER S_delete
ON STUDENT
FOR DELETE AS
DECLARE@rowcount int
SELECT@rowcount=@@ROWCOUNT
IF@rowcount>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('当前要删除的记录数为%d，一次只允许删除一行记录！',16,1,
@rowcount)
END
DECLARE@S_dname VARCHAR(16),@S_cno INT
SELECT@S_dname=DNAME,@S_cno=CNO
FROM DELETED
IF(@S_cno IN(SELECT CNO FROM TEACHER WHERE DNAME=@S_dname))
BEGIN
sql server 2000是一种
ROLLBACK TRANSACTION
RAISERROR('删除记录的课程为本系即%s系教师所开设，不允许删除！',16,1,
@S_dname)
END
当通过如下语句从STUDENT表中删除数据时：
DELETE STUDENT WHERE SNO=’9706’
运行结果如下。
当前要删除的记录数为4，一次只允许删除一行记录！
32916.2SQL Server中的触发器

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。