SQL Server触发器语法

admin管理员组

文章数量:1536056

2024年7月4日发(作者：)

SQL

触发器语法参考

CreateTRIGGERtrigger_name

ON{table|view}

[WITHENCRYPTION]

{

{{FOR|AFTER|INSTEADOF}{[Insert][,][Updat

e]}

[WITHAPPEND]

[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

是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中

必须唯一。可以选择是否指定触发器所有者名称。

Table|view

是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。

可以选择是否指定表或视图的所有者名称。

WITHENCRYPTION

加密syscomments表中包含CreateTRIGGER语句文本的条目。使

用WITHENCRYPTION可防止将触发器作为SQLServer复制的

一部分发布。

AFTER

指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后

才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执

如果仅指定FOR关键字，则AFTER是默认设置。

不能在视图上定义AFTER触发器。

INSTEADOF

指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操

作。

在表或视图上，每个Insert、Update或Delete语句最多可以定义一

个INSTEADOF触发器。然而，可以在每个具有INSTEADOF触

发器的视图上定义视图。

INSTEADOF触发器不能在WITHCHECKOPTION的可更新视

图上定义。如果向指定了WITHCHECKOPTION选项的可更新视

图添加INSTEADOF触发器，SQLServer将产生一个错误。用户

必须用AlterVIEW删除该选项后才能定义INSTEADOF触发器。

{[Delete][,][Insert][,][Update]}

字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组

合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。

对于INSTEADOF触发器，不允许在具有ONDelete级联操作引

用关系的表上使用Delete选项。同样，也不允许在具有ONUpdate

级联操作引用关系的表上使用Update选项。

WITHAPPEND

指定应该添加现有类型的其它触发器。只有当兼容级别是65或更低

时，才需要使用该可选子句。如果兼容级别是70或更高，则不必使

用WITHAPPEND子句添加现有类型的其它触发器（这是兼容级别

设置为70或更高的CreateTRIGGER的默认行为）。有关更多信息，

请参见sp_dbcmptlevel。

WITHAPPEND不能与INSTEADOF触发器一起使用，或者，如

果显式声明AFTER触发器，也不能使用该子句。只有当出于向后兼

容而指定FOR时（没有INSTEADOF或AFTER），才能使用WI

THAPPEND。以后的版本将不支持WITHAPPEND和FOR（将被

解释为AFTER）。

NOTFORREPLICATION

表示当复制进程更改触发器所涉及的表时，不应执行该触发器。

AS

是触发器要执行的操作。

sql_statement

是触发器的条件和操作。触发器条件指定其它准则，以确定Delete、

Insert或Update语句是否导致执行触发器操作。

当尝试Delete、Insert或Update操作时，Transact-SQL语句中指定

的触发器操作将生效。

触发器可以包含任意数量和种类的Transact-SQL语句。触发器旨在

根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发

器中的Transact-SQL语句常常包含控制流语言。CreateTRIGGER语

句中使用几个特殊的表：

触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用

户操作可能更改的行的旧值或新值。例如，若要检索deleted表中的

所有值，请使用：

Select*

FROMdeleted

如果兼容级别等于70，那么在Delete、Insert或Update触发器中，

SQLServer将不允许引用inserted和deleted表中的text、ntext或

image列。不能访问inserted和deleted表中的text、ntext和ima

ge值。若要在Insert或Update触发器中检索新值，请将inserted

表与原始更新表联接。当兼容级别是65或更低时，对inserted或d

eleted表中允许空值的text、ntext或image列，将返回空值；如果

这些列不可为空，则返回零长度字符串。

当兼容级别是80或更高时，SQLServer允许在表或视图上通过I

NSTEADOF触发器更新text、ntext或image列。

n

是表示触发器中可以包含多条Transact-SQL语句的占位符。对于IF

Update(column)语句，可以通过重复Update(column)子句包含多

列。

IFUpdate(column)

测试在指定的列上进行的Insert或Update操作，不能用于Delete

操作。可以指定多列。因为在ON子句中指定了表名，所以在IFU

pdate子句中的列名前不要包含表名。若要测试在多个列上进行的In

sert或Update操作，请在第一个操作后指定单独的Update(column)

子句。在Insert操作中IFUpdate将返回TRUE值，因为这些列插

入了显式值或隐性(NULL)值。

说明IFUpdate(column)子句的功能等同于IF、IF...ELSE或WH

ILE语句，并且可以使用END语句块。有关更多信息，请

参见控制流语言。

可以在触发器主体中的任意位置使用Update(column)。

column

是要测试Insert或Update操作的列名。该列可以是SQLServer支

持的任何数据类型。但是，计算列不能用于该环境中。有关更多信息，

IF(COLUMNS_UpdateD())

测试是否插入或更新了提及的列，仅用于Insert或Update触发器

中。COLUMNS_UpdateD返回varbinary位模式，表示插入或更新了

表中的哪些列。

COLUMNS_UpdateD函数以从左到右的顺序返回位，最左边的为最

不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二

列，依此类推。如果在表上创建的触发器包含8列以上，则COLU

MNS_UpdateD返回多个字节，最左边的为最不重要的字节。在Inse

rt操作中COLUMNS_UpdateD将对所有列返回TRUE值，因为这

些列插入了显式值或隐性(NULL)值。

可以在触发器主体中的任意位置使用COLUMNS_UpdateD。

bitwise_operator

是用于比较运算的位运算符。

updated_bitmask

是整型位掩码，表示实际更新或插入的列。例如，表t1包含列C1、

C2、C3、C4和C5。假定表t1上有Update触发器，若要检查列C

2、C3和C4是否都有更新，指定值14；若要检查是否只有列C2

有更新，指定值2。

comparison_operator

是比较运算符。使用等号(=)检查updated_bitmask中指定的所有列

是否都实际进行了更新。使用大于号(>)检查updated_bitmask中指

定的任一列或某些列是否已更新。

column_bitmask

是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。

注释

触发器常常用于强制业务规则和数据完整性。SQLServer通过表创

建语句（AlterTABLE和CreateTABLE）提供声明引用完整性(DR

I)；但是DRI不提供数据库间的引用完整性。若要强制引用完整性（有

关表的主键和外键之间关系的规则），请使用主键和外键约束（Alter

关键字）。如果触发器表存在约束，则在INSTEADOF触发器执行

之后和AFTER触发器执行之前检查这些约束。如果违反了约束，则

回滚INSTEADOF触发器操作且不执行（激发）AFTER触发器。

可用sp_settriggerorder指定表上第一个和最后一个执行的AFTER

触发器。在表上只能为每个Insert、Update和Delete操作指定一个

第一个执行和一个最后一个执行的AFTER触发器。如果同一表上还

有其它AFTER触发器，则这些触发器将以随机顺序执行。

如果AlterTRIGGER语句更改了第一个或最后一个触发器，则将除

去已修改触发器上设置的第一个或最后一个特性，而且必须用sp_set

triggerorder重置排序值。

只有当触发SQL语句（包括所有与更新或删除的对象关联的引用级

联操作和约束检查）成功执行后，AFTER触发器才会执行。AFTER

触发器检查触发语句的运行效果，以及所有由触发语句引起的Upda

te和Delete引用级联操作的效果。

触发器限制

CreateTRIGGER必须是批处理中的第一条语句，并且只能应用到一

个表中。

触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库

的外部对象。

如果指定触发器所有者名称以限定触发器，请以相同的方式限定表

名。

在同一条CreateTRIGGER语句中，可以为多种用户操作（如Inse

rt和Update）定义相同的触发器操作。

如果一个表的外键在Delete/Update操作上定义了级联，则不能在该

表上定义INSTEADOFDelete/Update触发器。

在触发器内可以指定任意的SET语句。所选择的SET选项在触发

器执行期间有效，并在触发器执行完后恢复到以前的设置。

与使用存储过程一样，当触发器激发时，将向调用应用程序返回结果。

若要避免由于触发器激发而向应用程序返回结果，请不要包含返回结

果的Select语句，也不要包含在触发器中进行变量赋值的语句。包含

向用户返回结果的Select语句或进行变量赋值的语句的触发器需要

特殊处理；这些返回的结果必须写入允许修改触发器表的每个应用程

序中。如果必须在触发器中进行变量赋值，则应该在触发器的开头使

Delete触发器不能捕获TRUNCATETABLE语句。尽管TRUNCA

TETABLE语句实际上是没有Where子句的Delete（它删除所有

行），但它是无日志记录的，因而不能执行触发器。因为TRUNCAT

ETABLE语句的权限默认授予表所有者且不可转让，所以只有表所

有者才需要考虑无意中用TRUNCATETABLE语句规避Delete触

发器的问题。

无论有日志记录还是无日志记录，WRITETEXT语句都不激活触发

器。

触发器中不允许以下Transact-SQL语句：

AlterDATABASECreateDATABASEDISKINIT

DISKRESIZEDropDATABASELOADDATABASE

LOADLOGRECONFIGURERESTOREDATABASE

RESTORELOG

说明由于SQLServer不支持系统表中的用户定义触发器，因此建

多个触发器

SQLServer允许为每个数据修改事件（Delete、Insert或Update）创

建多个触发器。例如，如果对已有Update触发器的表执行CreateT

RIGGERFORUpdate，则将创建另一个更新触发器。在早期版本中，

在每个表上，每个数据修改事件（Insert、Update或Delete）只允许

有一个触发器。

说明如果触发器名称不同，则CreateTRIGGER（兼容级别为70）

的默认行为是在现有的触发器中添加其它触发器。如果触发器名称相

同，则SQLServer返回一条错误信息。但是，如果兼容级别等于或

小于65，则使用CreateTRIGGER语句创建的新触发器将替换同一

类型的任何现有触发器，即使触发器名称不同。有关更多信息，请参

见sp_dbcmptlevel。

递归触发器

当在sp_dboption中启用recursivetriggers设置时，SQLServer还允

许触发器的递归调用。

递归触发器允许发生两种类型的递归：

间接递归

直接递归

使用间接递归时，应用程序更新表T1，从而激发触发器TR1，该触

发器更新表T2。在这种情况下，触发器T2将激发并更新T1。

使用直接递归时，应用程序更新表T1，从而激发触发器TR1，该触

发器更新表T1。由于表T1被更新，触发器TR1再次激发，依此类

推。

下例既使用了间接触发器递归，又使用了直接触发器递归。假定在表

T1中定义了两个更新触发器TR1和TR2。触发器TR1递归地更

新表T1。Update语句使TR1和TR2各执行一次。而TR1的执行

将触发TR1（递归）和TR2的执行。给定触发器的inserted和del

eted表只包含与唤醒调用触发器的Update语句相对应的行。

说明只有启用sp_dboption的recursivetriggers设置，才会发生上

每个触发器都应是自包含的。

禁用recursivetriggers设置只能禁止直接递归。若要也禁用间接递

归，请使用sp_configure将nestedtriggers服务器选项设置为0。

如果任一触发器执行了ROLLBACKTRANSACTION语句，则无论

嵌套级是多少，都不会进一步执行其它触发器。

嵌套触发器

触发器最多可以嵌套32层。如果一个触发器更改了包含另一个触发

器的表，则第二个触发器将激活，然后该触发器可以再调用第三个触

发器，依此类推。如果链中任意一个触发器引发了无限循环，则会超

出嵌套级限制，从而导致取消触发器。若要禁用嵌套触发器，请用s

p_configure将nestedtriggers选项设置为0（关闭）。默认配置允许

嵌套触发器。如果嵌套触发器是关闭的，则也将禁用递归触发器，与

sp_dboption的recursivetriggers设置无关。

延迟名称解析

SQLServer允许Transact-SQL存储过程、触发器和批处理引用编译

时不存在的表。这种能力称为延迟名称解析。但是，如果Transact-S

则只有当兼容级别设置（通过执行sp_dbcmptlevel设置）等于65时，

才会在创建时发出警告。如果使用批处理，则在编译时发出警告。如

果引用的表不存在，将在运行时返回错误信息。有关更多信息，请参

见延迟名称解析和编译。

权限

CreateTRIGGER权限默认授予定义触发器的表所有者、sysadmin固

定服务器角色成员以及db_owner和db_ddladmin固定数据库角色

成员，并且不可转让。

若要检索表或视图中的数据，用户必须在表或视图中拥有Select语

句权限。若要更新表或视图的内容，用户必须在表或视图中拥有Ins

ert、Delete和Update语句权限。

如果视图中存在INSTEADOF触发器，用户必须在该视图中有Ins

ert、Delete和Update特权，以对该视图发出Insert、Delete和Upd

ate语句，而不管实际上是否在视图上执行了这样的操作。

示例

A.使用带有提醒消息的触发器

当有人试图在titles表中添加或更改数据时，下例将向客户端显示一

说明消息50009是sysmessages中的用户定义消息。有关创建用

户定义消息的更多信息，请参见sp_addmessage。

USEpubs

IFEXISTS(SelectnameFROMsysobjects

Wherename='reminder'ANDtype='TR')

DropTRIGGERreminder

GO

CreateTRIGGERreminder

ONtitles

FORInsert,Update

ASRAISERROR(50009,16,10)

GO

B.使用带有提醒电子邮件的触发器

当titles表更改时，下例将电子邮件发送给指定的人员(MaryM)。

USEpubs

Wherename='reminder'ANDtype='TR')

DropTRIGGERreminder

GO

CreateTRIGGERreminder

ONtitles

FORInsert,Update,Delete

AS

p_sendmail'MaryM',

'Don''tforgettoprintareportforthedistributors.'

GO

C.在employee和jobs表之间使用触发器业务规则

由于CHECK约束只能引用定义了列级或表级约束的列，表间的任何

约束（在下例中是指业务规则）都必须定义为触发器。

下例创建一个触发器，当插入或更新雇员工作级别(job_lvls)时，该

触发器检查指定雇员的工作级别（由此决定薪水）是否处于为该工作

定义的范围内。若要获得适当的范围，必须引用jobs表。

USEpubs

IFEXISTS(SelectnameFROMsysobjects

DropTRIGGERemployee_insupd

GO

CreateTRIGGERemployee_insupd

ONemployee

FORInsert,Update

AS

/*Gettherangeoflevelforthisjobtypefromthejobstable.*/

DECLARE@min_lvltinyint,

@max_lvltinyint,

@emp_lvltinyint,

@job_idsmallint

Select@min_lvl=min_lvl,

@max_lvl=max_lvl,

@emp_lvl=_lvl,

@job_id=_id

_id=_i

d

_id=_id

IF(@job_id=1)and(@emp_lvl

BEGIN

RAISERROR('Jobid1expectsthedefaultlevelof10.',16,1)

10)

END

ELSE

IFNOT(@emp_lvlBETWEEN@min_lvlAND@max_lvl)

BEGIN

RAISERROR('Thelevelforjob_id:%dshouldbebetween%dan

d%d.',

16,1,@job_id,@min_lvl,@max_lvl)

ROLLBACKTRANSACTION

END

D.使用延迟名称解析

下例创建两个触发器以说明延迟名称解析。

USEpubs

IFEXISTS(SelectnameFROMsysobjects

Wherename='trig1'ANDtype='TR')

DropTRIGGERtrig1

GO

--Creatingatriggeronanonexistenttable.

CreateTRIGGERtrig1

onauthors

AS

_lname,_fname,

FROMauthorsaINNERJOINdoes_not_existx

_id=_id

GO

--Hereisthestatementtoactuallyseethetextofthetrigger.

,

FROMsysobjectsoINNERJOINsyscommentsc

=

='TR'='trig1'

--Creatingatriggeronanexistingtable,butwithanonexistent

--column.

USEpubs

IFEXISTS(SelectnameFROMsysobjects

Wherename='trig2'ANDtype='TR')

DropTRIGGERtrig2

GO

CreateTRIGGERtrig2

ONauthors

FORInsert,Update

DECLARE@faxvarchar(12)

Select@fax=phone

FROMauthors

GO

--Hereisthestatementtoactuallyseethetextofthetrigger.

,

FROMsysobjectsoINNERJOINsyscommentsc

=

='TR'='trig2'

E.使用COLUMNS_UpdateD

下例创建两个表：一个employeeData表和一个auditEmployeeData

表。人力资源部的成员可以修改employeeData表，该表包含敏感的

雇员薪水信息。如果更改了雇员的社会保险号码(SSN)、年薪或银行

帐户，则生成审核记录并插入到auditEmployeeData审核表。

通过使用COLUMNS_UpdateD()功能，可以快速测试对这些包含敏

感雇员信息的列所做的更改。只有在试图检测对表中的前8列所做的

更改时，COLUMNS_UpdateD()才起作用。

USEpubs

WhereTABLE_NAME='employeeData')

DropTABLEemployeeData

IFEXISTS(SelectTABLE_NAMEFROMINFORMATION_SCHEM

WhereTABLE_NAME='auditEmployeeData')

DropTABLEauditEmployeeData

GO

CreateTABLEemployeeData(

emp_idintNOTNULL,

emp_bankAccountNumberchar(10)NOTNULL,

emp_salaryintNOTNULL,

emp_SSNchar(11)NOTNULL,

emp_lnamenchar(32)NOTNULL,

emp_fnamenchar(32)NOTNULL,

emp_managerintNOTNULL

)

GO

CreateTABLEauditEmployeeData(

audit_log_iduniqueidentifierDEFAULTNEWID(),

audit_log_typechar(3)NOTNULL,

audit_emp_bankAccountNumberchar(10)NULL,

audit_emp_salaryintNULL,

audit_emp_SSNchar(11)NULL,

audit_usersysnameDEFAULTSUSER_SNAME(),

audit_changeddatetimeDEFAULTGETDATE()

)

GO

CreateTRIGGERupdEmployeeData

ONemployeeData

FORupdateAS

/*Checkwhethercolumns2,rall

ofcolumns2,3or4havebeenchanged,createanauditrecord.T

hebitmaskis:power(2,(2-1))+power(2,(3-1))+power(2,(4-1))=

checkifallcolumns2,3,and4areupdated,use=14inplaceof

>0(below).*/

IF(COLUMNS_UpdateD()&14)>0

/*UseIF(COLUMNS_UpdateD()&14)=14toseeifallofcolu

mns2,3,and4areupdated.*/

BEGIN

--AuditOLDrecord.

(audit_log_type,

audit_emp_id,

audit_emp_bankAccountNumber,

audit_emp_salary,

audit_emp_SSN)

Select'OLD',

_id,

_bankAccountNumber,

_salary,

_SSN

FROMdeleteddel

--AuditNEWrecord.

InsertINTOauditEmployeeData

(audit_log_type,

audit_emp_id,

audit_emp_bankAccountNumber,

audit_emp_salary,

audit_emp_SSN)

Select'NEW',

_id,

_salary,

_SSN

FROMinsertedins

END

GO

/*InsertinganewemployeedoesnotcausetheUpdatetriggertofire.

*/

InsertINTOemployeeData

VALUES(101,'USA-987-01',23000,'R-M53550M',N'Mendel',N'

Roland',32)

GO

/*Updatingtheemployeerecordforemployeenumber101tochang

ethesalaryto51000causestheUpdatetriggertofireandanauditt

railtobeproduced.*/

UpdateemployeeData

SETemp_salary=51000

Whereemp_id=101

GO

GO

/*Updatingtheemployeerecordforemployeenumber101tochang

eboththebankaccountnumberandsocialsecuritynumber(SSN)ca

usestheUpdatetriggertofireandanaudittrailtobeproduced.*/

UpdateemployeeData

SETemp_bankAccountNumber='133146A0',emp_SSN='R-M53

550M'

Whereemp_id=101

GO

Select*FROMauditEmployeeData

GO

F.使用COLUMNS_UpdateD测试8列以上

如果必须测试影响到表中前8列以外的列的更新时，必须使用UBS

TRING函数测试由COLUMNS_UpdateD返回的适当的位。下例测

试影响ers表中的第3、第5或第9列的更

新。

USENorthwind

GO

CreateTRIGGERtr1ONCustomers

FORUpdateAS

IF((SUBSTRING(COLUMNS_UpdateD(),1,1)=power(2,(3-1))

+power(2,(5-1)))

AND(SUBSTRING(COLUMNS_UpdateD(),2,1)=power(2,(1-1)))

)

PRINT'Columns3,5and9updated'

GO

UpdateCustomers

SETContactName=ContactName,

Address=Address,

Country=Country

GO

本文标签： 触发器语句使用指定操作