SQL触发器

第

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所示（针对SQLServer数据库系统而言）。

SQL

trigger表** …Insered表新数据INSERTDELETEtrigger表** …trigger表** …修改的数据Deleted表被删除数据Deleted表被删除数据Insered表新数据（修改的数据）UPDATE图16.1触发器测试表

deleted表存放了DELETE和UPDATE语句中相关行的副本。在DELETE或UPDATE语句的执行中，这些相关行从trigger表中移到了deleted表中。一般情况下，这两张表中无共同行。

insertded表存放了INSERT和UPDATE语句中的副本。在INSERT或UPDATE语句的执行中，这些新行同时被加到inserted表和trigger表中。inserted表中的行是trigger表中新行的副本。

一个UPDATE效果上等价于一个DELETE再接着一个INSERT。首先“旧”行被复制到deleted表中，然后新行被复制到trigger表和inserted表中。

16.2SQLServer中的触发器

当对某一表进行操作时，SQLServer就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合规则。触发器的作用就是保证参照完整性和数据的一致性。

16.2.1SQLServer触发器的种类

SQLServer2000及其以后的版本支持两种类型的触发器：AFTER触发器和INSTEADOF触发器。其中AFTER触发器即为前面介绍的UPDATE、INSERT、DELETE触发器。

INSTEADOF触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEADOF触发器，也可以在视图上定INSTEADOF触发器，但对同一操作只能定义一个INSTEADOF触发器。

16.2.2使用CREATETRIGGER命令创建触发器

在SQLServer中，可以采用CREATETRIGGER命令创建触发器。语法如下。

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{

{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}

第16章

SQL触发器

326

SQL

[NOTFORREPLICATION]

AS

[{IFUPDATE(column)

[{AND|OR}UPDATE(column)]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)

{comparison_operator}column_bitmask[...n]

}]

sql_statement[...n]}}

说明如下。

trigger_name：为用户要创建的触发器的名字，触发器的名字必须符合SQLServer的命名规则，且其名字在当前数据库中必须是惟一的。

Table、view：与触发器相关联的表或视图的名字，并且该表或视图必须已经在数据库中存在。

WITHENCRYPTION：表示对含有CREATETRIGGER文本的syscomments表进行加密，防止用户通过查询syscomments表获取触发器的代码。

AFTER：表示只有执行了指定的操作（INSERT、DELETE、或UPDATE）之后，触发器才被激活，执行触发器中的SQL语句。

FOR：表示为AFTER触发器，且该触发器仅能在表上创建。INSTEADOF：指定触发器为INSTEADOF触发器。

每个表最多只能有一个INSTEADOF（INSERT、UPDATE、DELETE）触发器。然而

说明可以为每个表创建多个视图，对每个视图都可以有不同的INSTEADOF触发器。有关INSTEADOF触发器的详细信息在16.2.6节会有详细介绍。

DELETE、INSERT、UPDATE：指明执行哪种操作，将激活触发器。至少要包含3种操作类型种的一种，也可以是3种操作语句的任意组合。其中三者的顺序不受，且各选项要用逗号隔开。

NOTFORREPLICATION：告诉DBMS，当复制表时，触发器不能被执行。AS：后面列出触发器将要执行的动作。

IFUPDATEcolumn：用来测定对某一确定列是INSERT操作还是UPDATE操作。如果要测试INSERT还是UPDATE操作的列多于一列，可用AND或OR逻辑连接向IFUPDATE子句添加所希望的附加列名。

IFCOLUMNS_UPDATED()：仅在INSERT和UPDATE类型的触发器中使用，检查列是被更新还是被插入。

bitwise_operator：代表位逻辑运算符，常用“&”。

updated_bitmask：表示列的整位掩码。其中最右边的位表示表或视图的第1列，左边第2位代表第2列，依此类推。

comparison_operator：表示比较操作符。可以是“=”或者“>”。“=”表示检查在updated_bitmask中定义的所有列是否都被更新，用“>”表示检查是否在updated_bitmask

327

16.2SQLServer中的触发器

SQL

中定义的某些列被更新。

column_bitmask：在IFCOLUMNS_UPDATED()子句中，要测试的是否被更新的列的序号的掩码。

为了便于理解，这里给出一个使用IFCOLUMNS_UPDATED()子句的例子。如果表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：代表包含在触发器中的处理语句。

当不再需要触发器时，可用DROPTRIGGER语句删除触发器。语法如下。

DROPTRIGGERtrigger_name[...n]

16.2.3INSERT触发器

实例1

创建INSERT触发器

为STUDENT表创建触发器S_insert，当向STUDENT表中插入数据时，要求学号必须以“97”开头，且课程号CNO必须在COURSE表中，否则取消插入操作。实例代码如下。

CREATETRIGGERS_insertONSTUDENTFORINSERTAS

DECLARE@S_noVARCHAR(4),@S_cnoINTSELECT@S_no=SNO,@S_cno=CNOFROMINSERTED

IF(LEFT(@S_no,2)!='97')BEGIN

ROLLBACKTRANSACTION

RAISERROR('输入的学号：%s不是97级的学生，请确认后重新录入！',16,1,@S_no)END

IF(@S_cnoNOTIN(SELECTCNOFROMCOURSE))BEGIN

ROLLBACKTRANSACTION

RAISERROR('输入的课程号：%d在COURSE表中不存在，请确认后重新录入！',16,1,@S_cno)END

当通过如下语句向STUDENT表中插入数据时：

INSERTINTOSTUDENTVALUES('9602','王永','机械工程','男',2,76,'必修')

由于插入数据的学号为“9602”，并不是以“97”开头，所以执行S_insert触发器时，将执行“ROLLBACKTRANSACTION”语句，取消完成的工作，并执行RAISERROR语句给出错误信息。

运行结果如下。

第16章SQL触发器

328

SQL

输入的学号：9602不是97级的学生，请确认后重新录入！

当通过如下语句向STUDENT表中插入数据时：

INSERTINTOSTUDENTVALUES('9702','王永','机械工程','男',12,76,'必修')

由于课程号12在COURSE表中不存在，所以执行S_insert触发器时，将执行“ROLLBACKTRANSACTION”语句，取消工作，并执行RAISERROR语句给出错误信息。运行结果：

输入的课程号：12在COURSE表中不存在，请确认后重新录入！

可以在一个表上创建多个触发器，数据库把一个表中所有触发器都看作同一事务的一部分。因此只要其中一个触发器执行了ROLLBACKTRANSACTION语句，那么所有的操作（与该INSERT语句有关）都将被取消。

16.2.4DELETE触发器

实例2

创建DELETE触发器

为STUDENT表创建DELETE触发器S_delete，当一次删除的记录大于一行或者删除记录的课程为本系教师所开设时，取消删除操作。实例代码：

CREATETRIGGERS_deleteONSTUDENTFORDELETEAS

DECLARE@rowcountint

SELECT@rowcount=@@ROWCOUNTIF@rowcount>1

BEGIN

ROLLBACKTRANSACTION

RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,

@rowcount)

END

DECLARE@S_dnameVARCHAR(16),@S_cnoINTSELECT@S_dname=DNAME,@S_cno=CNOFROMDELETED

IF(@S_cnoIN(SELECTCNOFROMTEACHERWHEREDNAME=@S_dname))

BEGIN

ROLLBACKTRANSACTION

RAISERROR('删除记录的课程为本系即%s系教师所开设，不允许删除！',16,1,

@S_dname)

END

当通过如下语句从STUDENT表中删除数据时：

DELETESTUDENTWHERESNO=’9706’

运行结果如下。

当前要删除的记录数为4,一次只允许删除一行记录！

329

16.2SQLServer中的触发器

SQL

当用户从STUDENT表执行DELETE语句时，DBMS就激活触发器S_delete，该触发器首先判断要删除记录的行数，只要多于一条，就取消删除操作。当删除的记录只有一行时，触发器继续判断删除的记录的课程是否为本系教师所开设，如果是，就取消删除操作，否则就完成删除操作。如下面的删除代码：

DELETESTUDENTWHERESNO=’9705’ANDCNO=8

运行结果如下。

删除记录的课程为本系即电子工程系教师所开设，不允许删除！

说明

在SQLServer中执行TRUNCATETABLE语句，从表中删除所有的行时，不会触发DELETE触发器。

16.2.5UPDATE触发器

特定的表上执行UPDATE语句时，会触发UPDATE触发器。UPDATE操作包括两个部分：先将需要更新的内容从表中删除，然后插入新值。因此UPDATE触发器同时涉及到删除表和插入表。

实例3创建UPDATE触发器

为COURSE表创建UPDATE触发器C_update，当COURSE表的CNO和CNAME列（第1、2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事务。当其余的列（第3、4、5列）被更新时，触发器将更新前后的数据写入C_UpInfo表中。实例代码如下。

1．创建跟踪信息表C_UpInfo

CREATETABLEC_UpInfo(

Oper_Timesmalldatetime,Date_TypeCHAR(3),CNOint,CNAMEchar(30),CTIMEint,SCOUNTint,CTESTsmalldatetime,)

2．创建UPDATE触发器C_update

CREATETRIGGERC_updateONCOURSE

FORUPDATEAS

IF（COLUMNS_UPDATED()&3）>0/*第1列或第2列被更新*//*掩码“11”用十进制表示即为“3”*/

BEGIN

ROLLBACKTRANSACTION

RAISERROR('COURSE表的CNO列和CNAME列中的数据不允许被更新！',16,1)

第16章

SQL触发器

330

SQL

END

IF（COLUMNS_UPDATED()&28）>0/*第3列、第4列或第5列被更新*//*掩码“11100”用十进制表示即为“28”*/

BEGIN

/*将DELETED表中存放的记录即更新前的记录存入UpInfo表*/

INSERTINTOC_UpInfo(Oper_Time,Date_Type,CNO,CNAME,CTIME,SCOUNT,

CTEST)

SELECTGETDATE(),’OLD’,del.CNO,del.CNAME,del.CTIME,del.SCOUNT,

del.CTEST

FROMDELETEDASdel

/*将INSERTED表中存放的记录,即更新后的记录存入UpInfo表*/

INSERTINTOC_UpInfo(Oper_Time,Date_Type,CNO,CNAME,CTIME,SCOUNT,

CTEST)

SELECTGETDATE(),’NEW’,ins.CNO,ins.CNAME,ins.CTIME,ins.SCOUNT,

ins.CTEST

FROMINSERTEDASinsEND

当通过下面的代码更新COURSE表的CNAME列的值：

UPDATECOURSE

SETCNAME='线性代数基础'WHERECNO=4

运行结果如下。

COURSE表的CNO列和CNAME列中的数据不允许被更新！此时查看CNO＝4的课程名称：

SELECTCNAMEFROMCOURSEWHERECNO=4

运行结果如图16.2所示。

图16.2CNO＝4的课程名称

可见，CNAME列的值并没有被更新。而通过下面代码更新CNO和CNAME列以外的信息时：

UPDATECOURSE

SETCTIME=32,SCOUNT=90,CTEST='2006-9-15'WHERECNO=4

此时查看CNO=4的课程信息：

SELECT*FROMCOURSEWHERECNO=4

运行结果如图16.3所示：

331

16.2SQLServer中的触发器

SQL

图16.3

CNO=4的课程信息

可见，该记录已经被更新。

查看跟踪信息表C_UpInfo中的数据：

SELECT*FROMC_UpInfo

运行结果如图16.4所示。

图16.4表C_UpInfo中的数据

该表详细记录了COURSE表的更新时间以及更新前后的数据。

16.2.6INSTEADOF触发器

INSTEADOF触发器的优点是使不能被更新的视图支持更新操作。为了提高查询性能，视图通常来自多个表的结果集，但由12.3.1节的介绍可知，基于多表的视图不能被更新。而通过INSTEADOF触发器则可以实现这个功能。

实例4创建INSTEADOF触发器

假设有3个表（Stu97、Stu98、Stu99），分别存储97级、98级和99级的学生信息，3个表具有相同的结构。而视图Stu_View则包含了这3个表的所有学生信息。现为视图Stu_View创建INSTEADOF触发器Stu_Instead，实现能够直接向视图Stu_View中插入数据。实现代码如下。

1．创建表Stu97、Stu98和Stu99

CREATETABLEStu97(Snochar(5),

SnameCHAR(8),AgeINT)CREATETABLEStu98(Snochar(5),

SnameCHAR(8),AgeINT)CREATETABLEStu99(Snochar(5),

SnameCHAR(8),AgeINT)

2．创建视图Stu_View

CREATEVIEWStu_ViewAS

SELECT*FROMStu97UNIONALL

第16章SQL触发器

332

SQL

SELECT*FROMStu98UNIONALL

SELECT*FROMStu99

333

16.2SQLServer中的触发器

SQL

3．为视图Stu_View创建INSTEADOF触发器Stu_Instead

CREATETRIGGERStu_InsteadONStu_View

INSTEADOFINSERTAS

BEGIN

DECLARE@S_NOCHAR(2)

/*该变量用于存放插入数据的学号Sno的前两位，以判断插入记录属于哪张表*/SELECT@S_NO=SUBSTRING(Sno,1,2)FROMINSERTED

IF@S_NO='97'/*由学号判断该学生属于97级学生，记录插入Stu97表中*/BEGIN

INSERTINTOStu97

SELECTSno,Sname,AgeFROMINSERTEDRETURNEND

IF@S_NO='98'/*由学号判断该学生属于98级学生，记录插入Stu98表中*/BEGIN

INSERTINTOStu98

SELECTSno,Sname,AgeFROMINSERTEDRETURNEND

IF@S_NO='99'/*由学号判断该学生属于99级学生，记录插入Stu99表中*/BEGIN

INSERTINTOStu99

SELECTSno,Sname,AgeFROMINSERTEDENDELSEBEGIN

ROLLBACKTRANSACTION

RAISERROR('插入记录的学号信息不正确，请确认是97级、98级或99级学生的学号！

',16,1)

ENDEND

此时，通过下面的语句就可以直接向视图Stu_View中插入数据。

INSERTINTOStu_ViewVALUES(‘97005’,’王二明’，21)

执行过程中，系统首先执行触发器Stu_Instead，根据插入的学号“97005”的前两位“97”判断是97级的学生，因此就将记录插入到表Stu97中。此时，查看表Stu97中的数据，结果如图16.5所示。

SELECT*FROMStu97

图16.5表Stu97中的数据

第16章

SQL触发器

334

SQL

16.2.7嵌套触发器

当某一触发器执行时，能够触发另外一个触发器，这种情况称之为触发器嵌套。在执行过程中，如果一个触发器修改某个表，而这个表已经有其他触发器，这时就使用了嵌套触发器。在SQLServer中，触发器能够嵌套至32层。如果不需要嵌套触发器，可以通过sp_configure选项来进行设置。

说明

Oracle数据库不支持嵌套触发器。

实例5创建嵌套触发器

假如数据库中存在两个表，Book表和Author表，其结构和数据如图16.6所示。

Author表BOOK表图16.6Book表和Author表的结构和数据

为Author表创建DELETE触发器A_delete，实现每次只能从Author表中删除一条记录，同时删除Book表中与之对应的记录。为Book表创建DELETE触发器B_delete，每次只能删除Book表中的一条记录。实例代码如下。

/*为Author表创建DELETE触发器A_delete*/CREATETRIGGERA_deleteONAuthor

FORDELETEAS

DECLARE@rowcountint

SELECT@rowcount=@@ROWCOUNT/*记录删除操作所涉及的行数*/IF@rowcount>1/*删除的记录多于一行*/

BEGIN

ROLLBACKTRANSACTION/*回滚操作*/

RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,

@rowcount)

ENDELSE

DECLARE@AuthorCHAR(8)SELECT@Author=Author/*记录删除记录的Author列信息*/FROMDELETEDDELETEBook/*从Book表中删除Author对应的记录*/WHEREAuthor=@Author

/*为Book表创建DELETE触发器B_delete*/CREATETRIGGERB_delete

335

16.2SQLServer中的触发器

SQL

ONBook

FORDELETEAS

DECLARE@rowcountint

SELECT@rowcount=@@ROWCOUNT/*记录删除操作所涉及的行数*/IF@rowcount>1/*删除的记录多于一行*/

BEGIN

ROLLBACKTRANSACTION/*回滚操作*/

RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,

@rowcount)

ENDELSE

PRINT'Author表和Book表中相应的数据均被删除！'

当通过如下语句从Author表中删除记录时：

DELETEAuthorWHEREAuthor='李华'

系统首先激活触发器A_delete，在Author表中Author='李华'的记录共有一个，所以触发器A_delete将执行DELETE语句，从Book表中删除Author='李华'的记录。而对Book表进行DELETE操作时，基于Book表的DELETE触发器B_delete就被激活，它首先判断删除记录的行数。由图16.6可知，Author='李华'的记录共有两条，大于一条，所以触发器B_delete将执行ROLLBACKTRANSACTION操作，取消所有与DELETE有关的操作。

运行结果：

当前要删除的记录数为2,一次只允许删除一行记录！

此时，查看Author表中的数据如图16.7所示。

SELECT*FROMAuthor

图16.7Author表中的数据

可见，Author表中Author='李华'的记录并没有被删除，当然Book表中Author='李华'的记录也没有被删除。

而当通过如下语句从Author表中删除记录时：

DELETEAuthorWHEREAuthor='李彤'

由于在Author表和Book表中，Author='李彤'的记录都只有一条，所以触发器A_delete将执行DELETE语句，从Book表中删除Author='李彤'的记录。此时，触发器B_delete就被激活，删除记录为一条，所以删除操作成功执行。

运行结果：

Author表和Book表中相应的数据均被删除！

此时分别查看Author表和Book表中的数据如图16.8、图16.9所示。

第16章

SQL触发器

336

SQL

SELECT*FROMAuthorORDERBYAGE

图16.8Author表中的数据

SELECT*FROMBook

图16.9Book表中的数据

可见，Author表和Book表中，Author='李彤'的记录都被删除了。注意

如果Author表和Book表之间已经建立了主键和外键约束，则上述嵌套触发器就不能执行，其原因读者可自行分析。

16.2.8递归触发器

上节介绍了嵌套触发器，即由触发器Trigger1触发Trigger2，Trigger2又可以触发触发器Trigger3，……，而如果最后触发器Triggern又触发了触发器Trigger1，这就形成了递归触发器。

递归可以分为两种：间接递归和直接递归。举例解释如下，假如有表Table1、表Table2，在Table11、Table12上分别有触发器Trigger1、Trigger2。

间接递归：对Table1操作触发Trigger1，Trigger1对Table12操作从而触发Trigger2，Trigger2对Table1操作从而再次触发Trigger1，……。

直接递归：对Table1操作从而触发Trigger1，Trigger1对Table1操作从而再次触发Trigger1，……。

在默认情况下，SQLServer是禁止直接递归的。要使SQLServer支持直接递归，可采用如下两种方法进行设置。

1．通过系统存储过程sp_dboption进行设置语法如下。

sp_dboption'dbName','RecursiveTriggers',TRUE

说明：dbName为数据库名。如果把最后的参数由“True”变为“False”，则为禁止数据库直接递归操作。

2．通过EnterpriseManager进行设置

启动企业管理器r后，右击要设置的数据库，在弹出菜单中，选择“属性”命令，在弹

337

16.2SQLServer中的触发器

SQL

出的属性对话框中，选择“选项（Options）”标签页，选中“递归触发器（Recursivetriggers）”选项，单击“确定”按钮即可。如图16.10所示。

图16.10数据库属性对话框

下面就给出一个简单的直接递归的实例。

实例6创建递归触发器

假设数据库中有一个仓库库存表Storage，结构和数据如图16.11所示。

Storage表

编号号101102103104201202203物物品名称称洗衣粉香皂毛巾工作手套复印纸签字笔笔记本35503060102060库存袋块条副包只本单位位图16.11Storage表的结构和数据

为表Storage创建DELETE触发器Storage_delete，在该触发器中，一次只允许删除一条

记录，并删除与当前记录相连的下一条记录。即，如果当前要删除编号为“101”的记录，在触发器中就会查找与之相邻的下一条记录，也就是编号为“102”的记录，如果存在，则从表Storage中将其删除。实例代码如下。

CREATETRIGGERStorage_deleteONStorage

第16章

SQL触发器

338

SQL

FORDELETEAS

DECLARE@rowcountint

SELECT@rowcount=@@ROWCOUNT/*记录删除操作所涉及的行数*/IF@rowcount>1/*删除的记录多于一行*/

BEGIN

ROLLBACKTRANSACTION/*回滚操作*/

RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,

@rowcount)

END

IF@rowcount=1/*删除的记录为一行*/

BEGIN

DECLARE@S_NOint,@New_NOintSELECT@S_NO=编号/*记录删除记录的编号信息*/FROMDELETED

SELECT@New_NO=@S_NO+1DELETEStorage/*删除记录*/WHERE编号=@New_NOEND

当执行如下代码从Storage表中删除记录时：

DELETEStorage

WHERE编号=102

由于对Storage表进行了DELETE操作，所以基于Storage表的DELETE触发器Storage_delete就被触发。该触发器首先判断删除记录的行数，这里编号为102的记录只有一行，所以触发器将执行从Storage表中，删除下一个编号的记录（即编号为103的记录）。而此时由于又对Storage表进行了DELETE操作，所以Storage_delete触发器再次被触发，判断删除记录的行数，Storage表中编号为103的记录也只有一条，所以接着执行从Storage表中编号为104的记录，……。这样，就形成了直接递归的触发器。

最终查看表Storage中的数据如图16.12所示。

SELECT*FROMSTORAGE

图16.12表Storage中的数据

与图16.11中的数据对比，Storage表中编号为102～104之间的3条记录均被删除。

16.2.9SQLServer中触发器的管理

如果要显示触发器有哪些操作，必须查看触发器信息。在SQLServer中，有多种方法。这里介绍两种常用的方法：通过企业管理器以及通过系统存储过程sp_help、sp_helptext和sp_depends。

通过企业管理器非常简单。启动企业管理器，在要查询的表上单击右键，在弹出菜单中

339

16.2SQLServer中的触发器

SQL

选择“所有任务”→“管理触发器”，会弹出图16.13所示的对话框。在名称（Name）下拉菜单中列出了基于该表的所有触发器，选择查询的触发器，在文本（Text）窗口中就会显示该触发器定义的详细代码。

图16.13触发器属性对话框

下面介绍一下通过系统存储过程查询触发器的各种信息。1．sp_help

使用sp_help命令可以查询触发器的基本信息，如触发器的名字、属性、类型、创建时间等。

实例7使用sp_help命令查询触发器的基本信息

利用系统存储过程sp_help，查询触发器Stroage_delete的基本信息，代码如下：

sp_helpStroage_delete

运行结果如图16.14所示。

图16.14Stroage_delete触发器的基本信息

2．sp_helptext

使用sp_helptext命令能够查看触发器的正文信息，即触发器的创建语句。

实例8使用sp_helptext命令查看触发器的正文信息

利用系统存储过程sp_helptext，查询触发器Stroage_delete的基本信息，代码如下。

sp_helptextStroage_delete

运行结果如图16.15所示。

第16章

SQL触发器

340

SQL

图16.15触发器Stroage_delete的基本信息

3．sp_depends

通过sp_depends命令能够查看指定触发器所引用的表，或指定的表涉及到的所有触发器。

实例9通过sp_depends命令查询指定的表涉及到的所有触发器

利用系统存储过程sp_depends，查看TEACHER表涉及到的所有触发器，代码如下。

sp_dependsTEACHER

运行结果如图16.16所示。

图16.16TEACHER表涉及到的所有触发器

可见，结果中不仅包含了TEACHER表所涉及到的触发器，还包含了所涉及的存储过程和用户自定义的函数。

16.3Oracle数据库中触发器的操作

在Oracle系统里，触发器类似过程和函数，都有声明、执行和异常处理。Oracle将触发器的功能扩展到了Oracle系统事件，如数据库的启动与关闭等。

16.3.1Oracle触发器类型

在Oracle有3种类型的触发器：DML触发器、替代触发器和系统触发器。

341

16.2SQLServer中的触发器

SQL

DML触发器：Oracle可以在DML语句（INSERT、UPDATE、DELETE）进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句上进行触发。替代触发器（INSTEADOF）：与SQLServer中的INSTEADOF触发器类似，由于在Oracle里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。系统触发器：从Oracle8i开始，提供了第三种类型的触发器叫系统触发器。它可以在Oracle的事件中进行触发，如Oracle系统的启动与关闭等。

16.3.2触发器的创建

在Oracle中，触发器的创建也是通过CREATETRIGGER语句来实现的，但与SQLServer中的触发器创建语法有较大的差别。语法如下。

CREATETRIGGERtrigger_name[BEFORE|AFTER]trigger_eventONtable_reference

[FOREACHROW[WHENtrigger_condition]]trigger_body

说明如下。

trigger_name：为触发器的名字。在Oracle中，触发器名与存储过程名字不一样，它是单独的名字空间，因而触发器名可以和表或存储过程有相同的名字。BEFORE|AFTER：指明了触发器是在数据修改前（BEFORE），还是修改后（AFTER）被调用。

trigger_event：为触发器事件，可以是INSERT、UPDATE或DELETE。如果要创建替代触发器，则只需在触发事件前加上关键词INSTEADOF即可。ON：子句则包含了目标表的名称，也就是触发器应用的表。

FOREACHROW：指明每次插入、更新或删除一行时就调用触发器。

WHEN：是可选的，可以定义搜索条件，来调用触发器时的搜索范围。

trigger_body：为触发器执行的SQL语句，这些语句必须被放在BEGIN……END块中。另外，在Oracle中，触发器的应用受到一定的，主要的条件有以下几个。

触发器中可以包括DML语句，但不能使用控制语句、COMMIT语句、ROLLBACK语句、SVAEPOINT语句。然而，对于“系统触发器”，则可以使用CREATE语句、ALTER语句或者DROP语句。

由触发器所调用的存储过程或函数也不能使用控制语句。触发器中不能使用LONG、LONGRAW数据类型。下面给出一个简单的DML触发器的实例。

实例10DML触发器的创建

创建DML触发器del_emp，，当数据库中有记录删除时，把被删除记录写到日志表中。实例代码如下。

--创建删除日志记录表del_tempCREATETABLEdel_temp(

第16章

SQL触发器

342

SQL

empnamevarchar2(8),empdepartvarchar2(8),empsexchar(2),empagenumber,statusvarchar(8));--创建触发器del_emp

CREATETRIGGERdel_emp

BEFOREdeleteONemployeeforeachrowBEGIN

--将修改前数据插入到日志记录表del_emp,以供监督使用。

INSERTINTOdel_temp(empname,empdepart,empsex,empage,status)VALUES(:old.ename,:old.department,:old.sex,:old.age,'已删除');

END;

从employee表中删除职工王静的纪录。

DELETEemployeeWHEREename='王静';

此时employee表中的数据如下。

SQL>SELECT*FROMemployee;ENAMEDEPARTMENTSEAGE--------------------------------王东生产科男45李霞销售科女32刘俊财务科男32刘静人事科女25高伟生产科男28李春生产科女48李东销售科男19刘凯生产科男43

可见，职工王静的纪录确实被删除。而此时职工表删除日志表del_emp的数据如下：

SQL>SELECT*FROMdel_temp;EMPNAMEEMPDEPAREMEMPAGESTATUS------------------------------------王静生产科女34已删除

在Oracle中，INSTEADOF触发器的用法与16.2.6节介绍的SQLServer数据库中的INSTEADOF触发器基本相同，这里就不再详细介绍。

16.3.3创建系统触发器

从Oracle8i开始，Oracle提供了系统触发器。数据库系统事件包括数据库服务器的启动或关闭、用户的登录与退出、数据库服务错误等。语法如下。

CREATETRIGGER[sachema.]trigger_name{BEFORE|AFTER}

{ddl_event_list|database_event_list}ON{DATABASE|[schema.]SCHEMA}[when_clause]trigger_body;

说明如下。

ddl_event_list：一个或多个DDL事件，事件间用“OR”分开。

343

16.2SQLServer中的触发器

SQL

database_event_list：一个或多个数据库事件，事件间用“OR”分开。

其余的可参见16.3.2节的语法。系统触发器的种类和事件出现前后如表16-1所示。

表16-1

事启动关闭服务器错误登录注销创建撤消变更件之后之前之后之后之前之前，之后之前，之后之前，之后系统触发器的种类和事件允许的时机实例启动时激活实例正常关闭时激活只要有错误就激活成功登录后激活开始注销时激活在创建之前或之后激活在撤消之前或之后激活在变更之前或之后激活说明实例11创建系统触发器

建立一个触发器logALLconnects，当数据库中有用户登录时，将用户登录信息写到日志表login_temp中。实例代码如下。

--创建日志记录表login_tempCREATETABLElogin_temp(

user_namevarchar2(12),describevarchar2(10));--创建触发器logALLconnects

CREATETRIGGERlogALLconnectsAFTERLOGONONSCHEMABEGIN

INSERTINTOlogin_tempVALUES(USER,'登录数据库');ENDlogALLconnects;

当以SYSTEM身份重新登录数据库时，表login_temp中数据如下。

SQL>SELECT*FROMlogin_temp;USER_NAMEDESCRIBE----------------------SYSTEM登录数据库

16.3.4触发器的触发次序和触发谓词的使用

在Oracle中，每张表最多可建立12个触发器，它们是：

BEFOREBEFOREAFTERAFTERBEFOREBEFOREAFTER

第16章

SQL触发器

INSERT

INSERTFOREACHROWINSERT

INSERTFOREACHROWUPDATE

UPDATEFOREACHROWUPDATE

344

SQL

AFTERBEFOREBEFOREAFTERAFTER

UPDATEFOREACHROWDELETE

DELETEFOREACHROWDELETE

DELETEFOREACHROW

但是它们的触发是有次序的，基本触发次序如下。

（1）首先执行（触发）的是BEFORE语句级的触发器，即BEFOREINSERT、BEFOREUPDATE或者BEFOREDELETE触发器。

（2）接着执行带有FOREACHROW子句的触发器，这其中的执行顺序可表示为：

执行BEFORE语句行级触发器；执行DML语句；

执行AFTER语句行级触发器。

（3）最后系统才执行AFTER语句级的触发器，即AFTERINSERT、AFTERUPDATE或者AFTERDELETE触发器。

Oracle提供3个参数：INSERTING、UPDATEING和DELETING，用于判断触发了哪些操作。这3个谓词的功能如表16-2所示。

表16-2谓INSERTINGUPDATINGDELETING词触发谓词及其功能

行为如果触发语句是INSERT语句，则为True,否则为False如果触发语句是UPDATE语句，则为True,否则为False如果触发语句是DELETE语句，则为True,否则为False下面通过一个实例说明触发谓词的使用。

实例12触发器的触发次序和触发谓词的使用

建立一个触发器Emp_Changes，当职工表employee中的数据被改变（更新、插入或删除）时，将操作的时间、操作的类型以及更改前后的数据记录到日志表chang_temp中。

实例代码如下。

--创建日志表Emp_ChangesCREATETABLEchang_temp(

date_typevarchar2(6),change_typevarchar2(6),timestampdate,

empnamevarchar2(8),empdepartvarchar2(8),empsexchar(2),empagenumber);--创建触发器

CREATETRIGGEREmp_Changes

BEFOREINSERTORDELETEORUPDATEONemployeeFOREACHROWDECLARE

v_ChangeTypeCHAR(4);BEGIN

IFINSERTINGTHEN

v_ChangeType:='插入';ELSIFUPDATINGTHEN

16.2

SQLServer中的触发器

345

SQL

v_ChangeType:='更新';ELSE

v_ChangeType:='删除';ENDIF;

--在chang_temp表中记录所有的改变，使用sysdate来产生系统时间INSERTINTOchang_temp

(date_type,change_type,timestamp,empname,empdepart,empsex,empage)VALUES

('旧数据',v_ChangeType,

SYSDATE,:old.ename,:old.department,:old.sex,:old.age);

INSERTINTOchang_temp

(date_type,change_type,timestamp,empname,empdepart,empsex,empage)VALUES

('新数据',v_ChangeType,

SYSDATE,:new.ename,:new.department,:new.sex,:new.age);

ENDEmp_Changes;

当通过下列语句向表employee中更新、插入和删除数据时：

UPDATEemployeeSETdepartment='销售科'WHEREename='高伟';INSERTINTOemployeeVALUES('王静','生产科','女',34);DELETEemployeeWHEREename='李春';

日志表chang_temp中的数据如下。

SQL>SELECT*FROMchang_temp;

DATE_TCHANGETIMESTAMPEMPNAMEEMPDEPAREM----------------------------------------旧数据更新30-7月-06高伟生产科男新数据更新30-7月-06高伟销售科男旧数据插入30-7月-06

新数据插入30-7月-06王静生产科女旧数据删除30-7月-06李春生产科女新数据删除30-7月-06

EMPAGE----------28283448

注意

通过DELETE语句从表employee表中删除数据时，不仅Emp_Changes触发器被触发，16.3.2节创建的del_emp触发器也被触发。

此时del_temp表中的数据如下。

SQL>SELECT*FROMdel_temp;

EMPNAMEEMPDEPAREMEMPAGESTATUS------------------------------------王静生产科女34已删除李春生产科女48已删除

可见删除的记录同样也被写入表del_temp中。最后，employee表中的数据如下。

SQL>SELECT*FROMemployee;ENAMEDEPARTMENTSEAGE--------------------------------第16章

SQL触发器

346

SQL

王东李霞刘俊刘静高伟李东刘凯王静

生产科销售科财务科人事科销售科销售科生产科生产科

男女男女男男男女

4532322528194334

16.3.5Oracle触发器的管理

1．禁用/启用触发器

可以通过ALTER语句禁用或启用触发器，有两种方式：ALTERTRIGGER语句和ALTERTABLE语句。语法如下。

ALTERTRIGGER[user.]trigger{ENABLE|DISABLE}

ALTERTABLE[user.]table{ENABLE|DISABLE}ALLTRIGGERS

说明：ENABLE关键字表示启用触发器，DISABLE关键字则表示禁用触发器。

实例13禁用触发器

禁用employee表上的所有触发器。实例代码：

ALTERTABLEemployeeDISABLEALLTRIGGERS;

2．删除触发器

可以采用DROPTRIGGER语句删除触发器。语法如下。

DROPTRIGGERtrigger_name

实例14删除触发器

从数据子字典中查询所有触发器，并删除触发器DEL_EMP。实例代码：

SQL>selecttrigger_namefromuser_triggers;

运行结果如下。

TRIGGER_NAME

------------------------------DEF$_PROPAGATOR_TRIGDEL_EMP

EMP_CHANGESREPCATLOGTRIGLOGALLCONNECTS

SQL>DROPTRIGGERDEL_EMP;触发器已丢弃

3．查询触发器的内容

当创建了触发器后，它的源代码就存放在数据字典中。与触发器有关的数据字典有：

347

16.2SQLServer中的触发器

SQL

USER_TRIGGERS、ALL_TRIGGERS和DBA_TRIGGERS。这里只介绍DBA_TRIGGERS，其各列存储的信息如表16-3所示。

表16-3列OwnerTrigger_NameTrigger_Type名DBA_TRIGGERS数据字典的内容

数据类型Varchar2(30)Varchar2(30)Varchar2(16)是否空触发器的主人触发器名称触发器类型，可以是：Beforeeachrow,AftereachrowBeforestatement,AfterstatementTriggering_eventTable_ownerTable_nameReferencing_nameWhen_clausestatusDescriptionTrigger_bodyVarchar2(26)Varchar2(30)Varchar2(30)Varchar2(87)Varchar2(4000)Varchar2(8)Varchar2(4000)longNotnullNotnullNotnull语句，可以是INSERT,UPDATE,DELETE表的主人表名引用名，可以是OLD或NEWWhen条件如果是DISABLE，则不触发触发器描述当触发时要进行的动作说明NotnullNotnull实例15使用DBA_TRIGGERS数据字典

从DBA_TRIGGERS数据字典中查询触发器EMP_CHANGES的相关信息。实例代码：

SELECTTrigger_Name,Triggering_event,Table_nameFROMDBA_TRIGGERSWHERETrigger_Name=‘EMP_CHANGES’;

运行结果如下。

TRIGGER_NAMETRIGGERING_EVENT

-----------------------------------------EMP_CHANGESINSERTORUPDATEORDELETE

TABLE_NAME

-------------EMPLOYEE

第16章SQL触发器

348