如何创建sql触发器.docx

如何创建sql触发器.docx

《如何创建sql触发器.docx》由会员分享，可在线阅读，更多相关《如何创建sql触发器.docx（18页珍藏版）》请在冰豆网上搜索。

如何创建sql触发器

触发器

语法

　　CREATETRIGGERtrigger_name

　　ON{table|view}

　　[WITHENCRYPTION]

　　{

{{FOR|AFTER|INSTEADOF}

{[INSERT][DELETE][UPDATE]}

　　[WITHAPPEND]

　　[NOTFORREPLICATION]

　　AS

　　[{IFUPDATE（column）

　　[{AND|OR}UPDATE（column）]

　　[...n]

　　|IF（COLUMNS_UPDATED（）updated_bitmask）

　　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），才能使用WITHAPPEND。

以后的版本将不支持WITHAPPEND和FOR（将被解释为AFTER）。

　　NOTFORREPLICATION

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

　　AS

　　是触发器要执行的操作。

　　sql_statement

　　是触发器的条件和操作。

触发器条件指定其它准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。

　　当尝试DELETE、INSERT或UPDATE操作时，Transact-SQL语句中指定的触发器操作将生效。

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

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

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

CREATETRIGGER语句中使用几个特殊的表：

　　*deleted和inserted是逻辑（概念）表。

这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。

例如，若要检索deleted表中的所有值，请使用：

　　SELECT*

　　FROMdeleted

　　*如果兼容级别等于70，那么在DELETE、INSERT或UPDATE触发器中，SQLServer将不允许引用inserted和deleted表中的text、ntext或image列。

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

若要在INSERT或UPDATE触发器中检索新值，请将inserted表与原始更新表联接。

当兼容级别是65或更低时，对inserted或deleted表中允许空值的text、ntext或image列，将返回空值；如果这些列不可为空，则返回零长度字符串。

　　当兼容级别是80或更高时，SQLServer允许在表或视图上通过INSTEADOF触发器更新text、ntext或image列。

　　n

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

对于IFUPDATE（column）语句，可以通过重复UPDATE（column）子句包含多列。

　　IFUPDATE（column）

　　测试在指定的列上进行的INSERT或UPDATE操作，不能用于DELETE操作。

可以指定多列。

因为在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。

若要测试在多个列上进行的INSERT或UPDATE操作，请在第一个操作后指定单独的UPDATE（column）子句。

在INSERT操作中IFUPDATE将返回TRUE值，因为这些列插入了显式值或隐性（NULL）值。

　　说明IFUPDATE（column）子句的功能等同于IF、IF...ELSE或WHILE语句，并且可以使用BEGIN...END语句块。

有关更多信息，请参见控制流语言。

　　可以在触发器主体中的任意位置使用UPDATE（column）。

　　column

　　是要测试INSERT或UPDATE操作的列名。

该列可以是SQLServer支持的任何数据类型。

但是，计算列不能用于该环境中。

有关更多信息，请参见数据类型。

　　IF（COLUMNS_UPDATED（））

　　测试是否插入或更新了提及的列，仅用于INSERT或UPDATE触发器中。

COLUMNS_UPDATED返回varbinary位模式，表示插入或更新了表中的哪些列。

　　COLUMNS_UPDATED函数以从左到右的顺序返回位，最左边的为最不重要的位。

最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。

如果在表上创建的触发器包含8列以上，则COLUMNS_UPDATED返回多个字节，最左边的为最不重要的字节。

在INSERT操作中COLUMNS_UPDATED将对所有列返回TRUE值，因为这些列插入了显式值或隐性（NULL）值。

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

　　bitwise_operator

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

　　updated_bitmask

　　是整型位掩码，表示实际更新或插入的列。

例如，表t1包含列C1、C2、C3、C4和C5。

假定表t1上有UPDATE触发器，若要检查列C2、C3和C4是否都有更新，指定值14；若要检查是否只有列C2有更新，指定值2。

　　comparison_operator

　　是比较运算符。

使用等号（=）检查updated_bitmask中指定的所有列是否都实际进行了更新。

使用大于号（>）检查updated_bitmask中指定的任一列或某些列是否已更新。

　　column_bitmask

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

　　注释

　　触发器常常用于强制业务规则和数据完整性。

SQLServer通过表创建语句（ALTERTABLE和CREATETABLE）提供声明引用完整性（DRI）；但是DRI不提供数据库间的引用完整性。

若要强制引用完整性（有关表的主键和外键之间关系的规则），请使用主键和外键约束（ALTERTABLE和CREATETABLE的PRIMARYKEY和FOREIGNKEY关键字）。

如果触发器表存在约束，则在INSTEADOF触发器执行之后和AFTER触发器执行之前检查这些约束。

如果违反了约束，则回滚INSTEADOF触发器操作且不执行（激发）AFTER触发器。

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

在表上只能为每个INSERT、UPDATE和DELETE操作指定一个第一个执行和一个最后一个执行的AFTER触发器。

如果同一表上还有其它AFTER触发器，则这些触发器将以随机顺序执行。

　　如果ALTERTRIGGER语句更改了第一个或最后一个触发器，则将除去已修改触发器上设置的第一个或最后一个特性，而且必须用sp_settriggerorder重置排序值。

　　只有当触发SQL语句（包括所有与更新或删除的对象关联的引用级联操作和约束检查）成功执行后，AFTER触发器才会执行。

AFTER触发器检查触发语句的运行效果，以及所有由触发语句引起的UPDATE和DELETE引用级联操作的效果。

　　触发器限制

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

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

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

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

　　如果一个表的外键在DELETE/UPDATE操作上定义了级联，则不能在该表上定义INSTEADOFDELETE/UPDATE触发器。

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

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

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

若要避免由于触发器激发而向应用程序返回结果，请不要包含返回结果的SELECT语句，也不要包含在触发器中进行变量赋值的语句。

包含向用户返回结果的SELECT语句或进行变量赋值的语句的触发器需要特殊处理；这些返回的结果必须写入允许修改触发器表的每个应用程序中。

如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SETNOCOUNT语句以避免返回任何结果集。

　　DELETE触发器不能捕获TRUNCATETABLE语句。

尽管TRUNCATETABLE语句实际上是没有WHERE子句的DELETE（它删除所有行），但它是无日志记录的，因而不能执行触发器。

因为TRUNCATETABLE语句的权限默认授予表所有者且不可转让，所以只有表所有者才需要考虑无意中用TRUNCATETABLE语句规避DELETE触发器的问题。

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

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

　　ALTERDATABASECREATEDATABASEDISKINIT

　　DISKRESIZEDROPDATABASELOADDATABASE

　　LOADLOGRECONFIGURERESTOREDATABASE

　　RESTORELOG

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

　　多个触发器

　　SQLServer允许为每个数据修改事件（DELETE、INSERT或UPDATE）创建多个触发器。

例如，如果对已有UPDATE触发器的表执行CREATETRIGGERFORUPDATE，则将创建另一个更新触发器。

在早期版本中，在每个表上，每个数据修改事件（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和deleted表只包含与唤醒调用触发器的UPDATE语句相对应的行。

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

对于为给定事件定义的多个触发器，并没有确定的执行顺序。

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

　　禁用recursivetriggers设置只能禁止直接递归。

若要也禁用间接递归，请使用sp_configure将nestedtriggers服务器选项设置为0。

　　如果任一触发器执行了ROLLBACKTRANSACTION语句，则无论嵌套级是多少，都不会进一步执行其它触发器。

　　嵌套触发器

　　触发器最多可以嵌套32层。

如果一个触发器更改了包含另一个触发器的表，则第二个触发器将激活，然后该触发器可以再调用第三个触发器，依此类推。

如果链中任意一个触发器引发了无限循环，则会超出嵌套级限制，从而导致取消触发器。

若要禁用嵌套触发器，请用sp_configure将nestedtriggers选项设置为0（关闭）。

默认配置允许嵌套触发器。

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

　　延迟名称解析

　　SQLServer允许Transact-SQL存储过程、触发器和批处理引用编译时不存在的表。

这种能力称为延迟名称解析。

但是，如果Transact-SQL存储过程、触发器或批处理引用在存储过程或触发器中定义的表，则只有当兼容级别设置（通过执行sp_dbcmptlevel设置）等于65时，才会在创建时发出警告。

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

如果引用的表不存在，将在运行时返回错误信息。

有关更多信息，请参见延迟名称解析和编译。

　　权限

　　CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色成员以及db_owner和db_ddladmin固定数据库角色成员，并且不可转让。

　　若要检索表或视图中的数据，用户必须在表或视图中拥有SELECT语句权限。

若要更新表或视图的内容，用户必须在表或视图中拥有INSERT、DELETE和UPDATE语句权限。

如果视图中存在INSTEADOF触发器，用户必须在该视图中有INSERT、DELETE和UPDATE特权，以对该视图发出INSERT、DELETE和UPDATE语句，而不管实际上是否在视图上执行了这样的操作。

　　示例

　　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

　　IFEXISTS（SELECTnameFROMsysobjects

　　WHEREname='reminder'ANDtype='TR'）

　　DROPTRIGGERreminder

　　GO

　　CREATETRIGGERreminder

　　ONtitles

　　FORINSERT,UPDATE,DELETE

　　AS

　　EXECmaster..xp_sendmail'MaryM',

　　'Don''tforgettoprintareportforthedistributors.'

　　GO

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

由于CHECK约束只能引用定义了列级或表级约束的列，表间的任何约束（在下例中是指业务规则）都必须定义为触发器。

　　下例创建一个触发器，当插入或更新雇员工作级别（job_lvls）时，该触发器检查指定雇员的工作级别（由此决定薪水）是否处于为该工作定义的范围内。

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

　　USEpubs

　　IFEXISTS（SELECTnameFROMsysobjects

　　WHEREname='employee_insupd'ANDtype='TR'）

　　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=i.job_lvl,

　　@job_id=i.job_id

　　FROMemployeeeINNERJOINinsertediONe.emp_id=i.emp_id

　　JOINjobsjONj.job_id=i.job_id

　　IF（@job_id=1）and（@emp_lvl<>10）

　　BEGIN

　　RAISERROR（'Jobid1expectsthedefaultlevelof10.',16,1）

　　ROLLBACKTRANSACTION

　　END

　　ELSE

　　IFNOT（@emp_lvlBETWEEN@min_lvlAND@max_lvl）

　　BEGIN

　　RAISERROR（'Thelevelforjob_id:

%dshouldbebetween%dand%d.',

　　16,1,@job_id,@min_lvl,@max_lvl）

　　ROLLBACKTRANSACTION

　　END

　　D.使用延迟名称解析

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

　　USEpubs

　　IFEXISTS（SELECTnameFROMsysobjects

　　WHEREname='trig1'ANDtype='TR'）

　　DROPTRIGGERtrig1

　　GO

　　--Creatingatriggeronanonexistenttable.

　　CREATETRIGGERtrig1

　　onauthors

　　FORINSERT,UPDATE,DELETE

　　AS

　　SELECTa.au_lname,a.au_fname,x.info

　　FROMauthorsaINNERJOINdoes_not_existx

　　ONa.au_id=x.au_id

　　GO

　　--Hereisthestatementtoac