SQL触发器

 

SQL触发器

  触发器大凡同样栽特别类别的存储过程,不由用户直接调用。创建触发器时会指向那开展定义,以便在针对特定表或列作特定项目的多少修改时执行。

  CREATE PROCEDURE 或 CREATE TRIGGER 语句不能够超越批处理。即存储过程还是触发器始终只能以一个批处理中开创并编译到一个尽计划遭到。

  用触发器还可以强制执行业务规则

  Microsoft SQL Server™ 2000
提供了有限种植要机制来强制业务规则与数据完整性:约束与触发器。触发器是一致栽特殊类型的仓储过程,它于指定的表中的多寡发生变化时自动生效。唤醒调用触发器以响应
INSERT、UPDATE 或 DELETE 语句。触发器可以查询任何表,并可涵盖复杂的
Transact-SQL言语。将触发器和接触它的话语作为可于触发器内回滚的么事务对待。如果检测及严重错误(例如,磁盘空间不足),则通事情即自行回滚。

优点

  触发器可通过数据库被之相关表实现级联更改;不过,通过级联引用完整性约束好还实惠地履这些反。

  触发器可以强制比用 CHECK 约束定义之约束越来越复杂的约。

  与 CHECK
约束不同,触发器可以引用其它表中的排列。例如,触发器可以利用其它一个表中的
SELECT
比较插入或更新的数额,以及执行外操作,如修改数据或者显用户定义错误信息。

  触发器也得评估数据修改前后的表状态,并根据该区别采取对策。

  一个表中的几近独同类触发器(INSERT、UPDATE 或
DELETE)允许使用多单不等的机关以响应和一个改动报告句。

  比较触发器与约束

  约束与触发器在奇特情况下各个起优势。触发器的主要利益在叫它们可涵盖使用
Transact-SQL
代码的复杂处理逻辑。因此,触发器可以支撑自律之具有机能;但其当所给闹的成效及并无连续顶好的方。

  实体完整性总应在低于级别及经过索引进行强制,这些索引或是 PRIMARY KEY
和 UNIQUE 约束之一模一样有些,或是在约之外独立创建的。假设功能可满足应用程序的功能要求,域完整性应经
CHECK 约束进行强制,而引用完整性 (RI) 则答应通过 FOREIGN KEY
约束进行强制。

  以约所支撑之力量无法满足应用程序的效果要求时,触发器就多有用。例如:

  除非 REFERENCES 子句定义了级联引用操作,否则 FOREIGN KEY
约束只能为和任何一样排着之价了匹配的值来说明列值。

  CHECK
约束只能冲逻辑表达式或平等表中的旁一样排来说明列值。如果应用程序要求根据其他一个表中的列验证列值,则须采用触发器。

  约束只能通过正规的体系错误信息传递错误信息。如果应用程序要求祭(或会从中获益)自定义信息及比较复杂的错误处理,则须利用触发器。

  触发器可透过数据库中之相关表实现级联更改;不过,通过级联引用完整性约束好重新使得地推行这些改动。

  触发器可以禁止或回滚违反引用完整性的变更,从而取消所尝试的多寡修改。当更改外键且新值与主键不般配配时,此类触发器就可能有作用。例如,可以于
titleauthor.title_id 上缔造一个安插触发器,使它们以新值与
titles.title_id 中的某某值不配合配时回滚一个安插。不过,通常采用 FOREIGN
KEY 来达到这个目的。

  如果触发器表上存在约,则以 INSTEAD OF 触发器执行后而于 AFTER
触发器执行前检查这些约束。如果封锁破坏,则回滚 INSTEAD OF
触发器操作而不履 AFTER 触发器。

SQL触发器语法

  语法

  CREATE TRIGGER trigger_name

  ON { table | view }

  [ WITH ENCRYPTION ]

  {

  { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ DELETE ] [ UPDATE
] }

  [ WITH APPEND ]

  [ NOT FOR REPLICATION ]

  AS

  [ { IF UPDATE ( column )

  [ { AND | OR } UPDATE ( column ) ]

  [ …n ]

  | IF ( COLUMNS_UPDATED ( ) updated_bitmask )

  column_bitmask [ …n ]

  } ]

  sql_statement [ …n ]

  }

  }

  参数

  trigger_name

  是触发器的称号。触发器名称必须符合标识符规则,并且以数据库中要唯一。可以选是否指定触发器所有者名称。

  Table | view

  是当其上执行触发器的表明要视图,有时称触发器表或触发器视图。可以选是否指定表或视图的主人名称。

  WITH ENCRYPTION

  加密 syscomments 表备受含 CREATE TRIGGER 语句文本的条款。使用 WITH
ENCRYPTION 可防止以触发器作为 SQL Server 复制的同一片发布。

  AFTER

  指定触发器只有当触发 SQL
语句被指定的具备操作都已经成功实行后才激起。所有的援级联操作以及约束检查吗必成功做到后,才会实施这触发器。

  如果单纯指定 FOR 关键字,则 AFTER 是默认设置。

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

  INSTEAD OF

  指定执行触发器而无是执行触发 SQL 语句,从而替代触发语句的操作。

  在说明或视图上,每个 INSERT、UPDATE 或 DELETE 语句极多得定义一个
INSTEAD OF 触发器。然而,可以于每个拥有 INSTEAD OF
触发器的视图上定义视图。

  INSTEAD OF 触发器不能够于 WITH CHECK OPTION
的而更新视图上定义。如果为指定了 WITH CHECK OPTION 选项之而是更新视图添加
INSTEAD OF 触发器,SQL Server 将发生一个荒谬。用户要用 ALTER VIEW
删除该选项后才能够定义 INSTEAD OF 触发器。

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

  是指定在说明要视图上执行怎样数据修改报告句时将激活触发器的重点字。必须至少指定一个挑选。在触发器定义着允许以以自由顺序组合的这些重点字。如果指定的取舍项多于一个,需用逗号分隔这些选择。

  对于 INSTEAD OF 触发器,不允以装有 ON DELETE
级联操作引用关系的表上使用 DELETE 选项。同样,也非容许在拥有 ON UPDATE
级联操作引用关系之表上使用 UPDATE 选项。

  WITH APPEND

  指定相应加上现有项目的别样触发器。只有当兼容级别是 65
或重新低时,才得运用该只是选子句。如果匹配级别是 70 或再胜似,则不用下
WITH APPEND 子句添加现有项目的其余触发器(这是配合级别设置也 70 或再次强的
CREATE TRIGGER 的默认行为)。有关重新多信息,请参见 sp_dbcmptlevel。

  WITH APPEND 不克和 INSTEAD OF 触发器一起行使,或者,如果显式声明
AFTER 触发器,也无克采用该子句。只有当由为后相当而指定 FOR 时(没有
INSTEAD OF 或 AFTER),才能够动用 WITH APPEND。以后的本子将非支持 WITH
APPEND 以及 FOR(将受解说也 AFTER)。

  NOT FOR REPLICATION

  表示当复制进程更改触发器所波及的表时,不应实行该触发器。

  AS

  是触发器要执行之操作。

  sql_statement

  是触发器的准及操作。触发器条件指定其他准则,以确定 DELETE、INSERT
或 UPDATE 语句是否造成执行触发器操作。

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

  触发器可以分包自由数量以及项目之 Transact-SQL
语句。触发器旨在根据数量修改报告句检查或改变数据;它不应允拿数据返回给用户。触发器中之
Transact-SQL 语句常常包含控制流语言。CREATE TRIGGER
语句被采取几只奇特的发明:

  * deleted 和 inserted
是逻辑(概念)表。这些发明在结构上类似于概念触发器的阐明(也就是是在其间尝试用户操作的发明);这些发明用于保存用户操作可能移的行之旧值或新值。例如,若要寻找
deleted 表中之兼具值,请以:

  SELECT *

  FROM deleted

  * 如果匹配级别等 70,那么当 DELETE、INSERT 或 UPDATE
触发器中,SQL Server 将不允许引用 inserted 和 deleted 表中之 text、ntext
或 image 列。不克看 inserted 和 deleted 表中之 text、ntext 和 image
值。若要于 INSERT 或 UPDATE 触发器中寻觅新价值,请将 inserted
表与旧创新表联接。当兼容级别是 65 或还低时,对 inserted 或 deleted
表中允许空值的text、ntext 或 image
列,将赶回空值;如果这些列不可也空,则回零长度字符串。

  当兼容级别是 80 或又胜时,SQL Server 允许以表要视图上通过 INSTEAD OF
触发器更新 text、ntext 或 image 列。

  n

  是象征触发器中得蕴涵多长长的 Transact-SQL 语句的挤占位符。对于 IF UPDATE
(column) 语句,可以通过再 UPDATE (column) 子句包含多列。

  IF UPDATE (column)

  测试在指定的列上进行的 INSERT 或 UPDATE 操作,不克用来 DELETE
操作。可以指定多列。因为当 ON 子句被指定了表名,所以于 IF UPDATE
子句被的列名前不要含表名。若一旦测试于多独列上开展的 INSERT 或 UPDATE
操作,请于率先单操作后指定单独的 UPDATE(column) 子句。在 INSERT 操作中
IF UPDATE 将回来 TRUE 值,因为这些列插入了显式值或隐性 (NULL) 值。

  说明 IF UPDATE (column) 子句的效益雷同于 IF、IF…ELSE 或 WHILE
语句,并且可以运用 BEGIN…END 语句块。有关重新多信息,请参见控制流语言。

  可以以触发器主体中之妄动位置应用 UPDATE (column)。

  column

  是若测试 INSERT 或 UPDATE 操作的列名。该列可以是 SQL Server
支持之其它数据类型。但是,计算列不克用于该环境被。有关重新多信息,请参见数据类型。

  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

  是如检查的排列的整型位掩码,用来检查是不是已更新或插队了这些列。

  注释

  触发器常常用于强制业务规则及数据完整性。SQL Server
通过表明创建语句(ALTER TABLE 和 CREATE TABLE)提供声称引用完整性(DRI);但是
DRI
不提供数据库间的援完整性。若一旦强制引用完整性(有关表的主键和外键之间关系之平整),请以主键和外键约束(ALTER
TABLE 和 CREATE TABLE 的 PRIMARY KEY 和 FOREIGN KEY
关键字)。如果触发器表存在律,则当 INSTEAD OF 触发器执行后跟 AFTER
触发器执行前检查这些约束。如果违反了束缚,则回滚 INSTEAD OF
触发器操作且不履行(激发)AFTER 触发器。

  可用 sp_settriggerorder 指定表及率先只与末段一个执的 AFTER
触发器。在表上只能为每个 INSERT、UPDATE 和 DELETE
操作指定一个首先单执行及一个结尾一个推行之 AFTER
触发器。如果同一表上还发出另 AFTER
触发器,则这些触发器将以随机顺序执行。

  如果 ALTER TRIGGER
语句更改了第一独或最后一个触发器,则将除已经修改触发器上设置的第一只或最后一个特征,而且必须用
sp_settriggerorder 重置排序值。

  只有当触发 SQL
语句(包括有与创新或去的对象关联的援级联操作与束缚检查)成功执行后,AFTER
触发器才会实施。AFTER
触发器检查触发语句的运转效果,以及有着由触发语句引起的 UPDATE 和 DELETE
引用级联操作的效能。

  触发器限制

  CREATE TRIGGER 必须是批判处理中之首先长语句,并且只能使至一个表中。

  触发器只能在手上的数据库被开创,不过触发器可以引用当前数据库的表面对象。

  如果指定触发器所有者名称为限制触发器,请以平等之法界定表名。

  于一如既往条 CREATE TRIGGER 语句被,可以呢多种用户操作(如 INSERT 和
UPDATE)定义相同之触发器操作。

  如果一个阐明的外键在 DELETE/UPDATE
操作上定义了级联,则非克于该表上定义 INSTEAD OF DELETE/UPDATE 触发器。

  于触发器内可以指定任意的 SET 语句。所选取的 SET
选项于触发器执行中有效,并在触发器执行完毕后重操旧业至先的装。

  与行使存储过程一样,当触发器激发时,将朝着调用应用程序返回结果。若一旦避由于触发器激发而向应用程序返回结果,请不要含返回结果的
SELECT
语句,也不要含在触发器中进行变量赋值的言辞。包含向用户返回结果的
SELECT
语句或开展变量赋值的讲话的触发器需要新鲜处理;这些回的结果要写副允许修改触发器表的每个应用程序中。如果必须在触发器中开展变量赋值,则应于触发器的启幕用
SET NOCOUNT 语词以避免返回外结果集。

  DELETE 触发器不克捕获 TRUNCATE TABLE 语词。尽管 TRUNCATE TABLE
语句实在是从未 WHERE 子句的
DELETE(它去所有行),但她是凭日志记录的,因而不可知执行触发器。因为
TRUNCATE TABLE
语句之权柄默认授予表所有者且不可让,所以只有表所有者才要考虑无意中之所以
TRUNCATE TABLE 语词规避 DELETE 触发器的题目。

  无论生日记记录或无日志记录,WRITETEXT 语句都无激活触发器。

  触发器中莫允以下 Transact-SQL 语句:

  ALTER DATABASE CREATE DATABASE DISK INIT

  DISK RESIZE DROP DATABASE LOAD DATABASE

  LOAD LOG RECONFIGURE RESTORE DATABASE

  RESTORE LOG

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

  多只触发器

  SQL Server 允许吗每个数据修改事件(DELETE、INSERT 或
UPDATE)创建多独触发器。例如,如果对都发 UPDATE 触发器的表执行 CREATE
TRIGGER FOR
UPDATE,则以创造另一个翻新触发器。在早期版本被,在每个表上,每个数据修改事件(INSERT、UPDATE
或 DELETE)只允许生一个触发器。

  说明 如果触发器名称不同,则 CREATE TRIGGER(兼容级别为
70)的默认行为是以存活的触发器中补充加其余触发器。如果触发器名称一致,则
SQL Server 返回一长条错误信息。但是,如果配合级别等还是低于 65,则用
CREATE TRIGGER
语句创建的初触发器将替换同一种类的旁现有触发器,即使触发器名称不同。有关重新多信息,请参见
sp_dbcmptlevel。

  递归触发器

  当在 sp_dboption 中启用 recursive triggers 设置时,SQL Server
还同意触发器的递归调用。

  递归触发器允许发生两种档次的递归:

  * 间接递归

  * 直接递归

  使用间接递归时,应用程序更新表 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 的 recursive triggers
设置,才见面有上述行为。对于为加事件定义的大多单触发器,并无确定的履顺序。每个触发器都承诺是打包含的。

  禁用 recursive triggers
设置只能禁止直接递归。若使呢熬用间接递归,请动 sp_configure 将 nested
triggers 服务器选项设置为 0。

  如果听由一触发器执行了 ROLLBACK TRANSACTION
语句,则无论嵌套级是小,都未见面愈实施另外触发器。

  嵌套触发器

  触发器最多得嵌套 32
层。如果一个触发器更改了含蓄其他一个触发器的表明,则第二独触发器将激活,然后该触发器可以重新调用第三单触发器,依此类推。如果链中任意一个触发器引发了极致循环,则会高于嵌套级限制,从而致使取消触发器。若一旦禁用嵌套触发器,请用
sp_configure 将 nested triggers 选项设置为
0(关闭)。默认配置允许嵌套触发器。如果嵌套触发器是倒闭的,则也用禁用递归触发器,与
sp_dboption 的 recursive triggers 设置无关。

  延迟名称解析

  SQL Server 允许 Transact-SQL
存储过程、触发器和批判处理引用编译时莫在的阐发。这种能力称为延迟名称解析。但是,如果
Transact-SQL
存储过程、触发器或批处理引用在储存过程要触发器中定义之申,则只有当兼容级别设置(通过推行
sp_dbcmptlevel 设置)等于 65
时,才见面在开创时有警示。如果用批处理,则当编译时出警示。如果引用的申不设有,将在运作时回来错误信息。有关重新多信息,请参见延迟名称解析和编译。

  权限

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

  若使检索表或视图中之数,用户必须在说明或视图中有着 SELECT
语句权限。若使更新表或视图的内容,用户要以说明要视图中颇具
INSERT、DELETE 和 UPDATE 语句权限。

  如果视图中在 INSTEAD OF 触发器,用户须于拖欠视图中出
INSERT、DELETE 和 UPDATE 特权,以对该视图有 INSERT、DELETE 和 UPDATE
语句,而不论是实际上是否当视图上推行了这么的操作。

  示例

  A. 使用含提醒消息之触发器

  当有人试图在 titles 表中加上或改数据经常,下例将朝着客户端著同一长长的消息。

  说明 消息 50009 是 sysmessages
中之用户定义消息。有关创建用户定义消息的重新多信息,请参见
sp_addmessage。

  USE pubs

  IF EXISTS (SELECT name FROM sysobjects

  WHERE name = ‘reminder’ AND type = ‘TR’)

  DROP TRIGGER reminder

  GO

  CREATE TRIGGER reminder

  ON titles

  FOR INSERT, UPDATE

  AS RAISERROR (50009, 16, 10)

  GO

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

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

  USE pubs

  IF EXISTS (SELECT name FROM sysobjects

  WHERE name = ‘reminder’ AND type = ‘TR’)

  DROP TRIGGER reminder

  GO

  CREATE TRIGGER reminder

  ON titles

  FOR INSERT, UPDATE, DELETE

  AS

  EXEC master..xp_sendmail ‘MaryM’,

  ’Don”t forget to print a report for the distributors.’

  GO

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

  由于 CHECK
约束只能引用定义了列级或表级约束的排列,表间的别样自律(在下例中凡恃工作规则)都要定义为触发器。

  下例创建一个触发器,当插入或更新雇员工作级别 (job_lvls)
时,该触发器检查指定雇员的行事级别(由此决定薪水)是否处在为该工作定义的限制外。若使落当的限量,必须引用
jobs 表。

  USE pubs

  IF EXISTS (SELECT name FROM sysobjects

  WHERE name = ’employee_insupd’ AND type = ‘TR’)

  DROP TRIGGER employee_insupd

  GO

  CREATE TRIGGER employee_insupd

  ON employee

  FOR INSERT, UPDATE

  AS

  /* Get the range of level for this job type from the jobs table.
*/

  DECLARE @min_lvl tinyint,

  @max_lvl tinyint,

  @emp_lvl tinyint,

  @job_id smallint

  SELECT @min_lvl = min_lvl,

  @max_lvl = max_lvl,

  @emp_lvl = i.job_lvl,

  @job_id = i.job_id

  FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id

  JOIN jobs j ON j.job_id = i.job_id

  IF (@job_id = 1) and (@emp_lvl <> 10)

  BEGIN

  RAISERROR (‘Job id 1 expects the default level of 10.’, 16, 1)

  ROLLBACK TRANSACTION

  END

  ELSE

  IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)

  BEGIN

  RAISERROR (‘The level for job_id:%d should be between %d and %d.’,

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

  ROLLBACK TRANSACTION

  END

  D. 使用延缓名称解析

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

  USE pubs

  IF EXISTS (SELECT name FROM sysobjects

  WHERE name = ‘trig1’ AND type = ‘TR’)

  DROP TRIGGER trig1

  GO

  – Creating a trigger on a nonexistent table.

  CREATE TRIGGER trig1

  on authors

  FOR INSERT, UPDATE, DELETE

  AS

  SELECT a.au_lname, a.au_fname, x.info

  FROM authors a INNER JOIN does_not_exist x

  ON a.au_id = x.au_id

  GO

  – Here is the statement to actually see the text of the trigger.

  SELECT o.id, c.text

  FROM sysobjects o INNER JOIN syscomments c

  ON o.id = c.id

  WHERE o.type = ‘TR’ and o.name = ‘trig1’

  – Creating a trigger on an existing table, but with a nonexistent

  – column.

  USE pubs

  IF EXISTS (SELECT name FROM sysobjects

  WHERE name = ‘trig2’ AND type = ‘TR’)

  DROP TRIGGER trig2

  GO

  CREATE TRIGGER trig2

  ON authors

  FOR INSERT, UPDATE

  AS

  DECLARE @fax varchar(12)

  SELECT @fax = phone

  FROM authors

  GO

  – Here is the statement to actually see the text of the trigger.

  SELECT o.id, c.text

  FROM sysobjects o INNER JOIN syscomments c

  ON o.id = c.id

  WHERE o.type = ‘TR’ and o.name = ‘trig2’

  E. 使用 COLUMNS_UPDATED

  下例创建两只说明:一个 employeeData 表和一个 auditEmployeeData
表。人力资源部的分子好改 employeeData
表,该表包含敏感的雇员薪水信息。如果改变了雇员的社会保险号码
(SSN)、年薪或银行帐户,则变审核记录并插入到 auditEmployeeData 审核表。

  通过下 COLUMNS_UPDATED()
功能,可以高速测试对这些带有敏感雇员信息之列所做的更动。只有当试图检测对表中的前
8 列所召开的改动时,COLUMNS_UPDATED() 才自作用。

  USE pubs

  IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

  WHERE TABLE_NAME = ’employeeData’)

  DROP TABLE employeeData

  IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

  WHERE TABLE_NAME = ‘auditEmployeeData’)

  DROP TABLE auditEmployeeData

  GO

  CREATE TABLE employeeData (

  emp_id int NOT NULL,

  emp_bankAccountNumber char (10) NOT NULL,

  emp_salary int NOT NULL,

  emp_SSN char (11) NOT NULL,

  emp_lname nchar (32) NOT NULL,

  emp_fname nchar (32) NOT NULL,

  emp_manager int NOT NULL

  )

  GO

  CREATE TABLE auditEmployeeData (

  audit_log_id uniqueidentifier DEFAULT NEWID(),

  audit_log_type char (3) NOT NULL,

  audit_emp_id int NOT NULL,

  audit_emp_bankAccountNumber char (10) NULL,

  audit_emp_salary int NULL,

  audit_emp_SSN char (11) NULL,

  audit_user sysname DEFAULT SUSER_SNAME(),

  audit_changed datetime DEFAULT GETDATE()

  )

  GO

  CREATE TRIGGER updEmployeeData

  ON employeeData

  FOR update AS

  /*Check whether columns 2, 3 or 4 has been updated. If any or all
of columns 2, 3 or 4 have been changed, create an audit record. The
bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check
if all columns 2, 3, and 4 are updated, use = 14 in place of >0
(below).*/

  IF (COLUMNS_UPDATED() & 14) > 0

  /*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2,
3, and 4 are updated.*/

  BEGIN

  – Audit OLD record.

  INSERT INTO auditEmployeeData

  (audit_log_type,

  audit_emp_id,

  audit_emp_bankAccountNumber,

  audit_emp_salary,

  audit_emp_SSN)

  SELECT ‘OLD’,

  del.emp_id,

  del.emp_bankAccountNumber,

  del.emp_salary,

  del.emp_SSN

  FROM deleted del

  – Audit NEW record.

  INSERT INTO auditEmployeeData

  (audit_log_type,

  audit_emp_id,

  audit_emp_bankAccountNumber,

  audit_emp_salary,

  audit_emp_SSN)

  SELECT ‘NEW’,

  ins.emp_id,

  ins.emp_bankAccountNumber,

  ins.emp_salary,

  ins.emp_SSN

  FROM inserted ins

  END

  GO

  /*Inserting a new employee does not cause the UPDATE trigger to
fire.*/

  INSERT INTO employeeData

  VALUES ( 101, ‘USA-987-01’, 23000, ‘R-M53550M’, N’Mendel’,
N’Roland’, 32)

  GO

  /*Updating the employee record for employee number 101 to change
the salary to 51000 causes the UPDATE trigger to fire and an audit trail
to be produced.*/

  UPDATE employeeData

  SET emp_salary = 51000

  WHERE emp_id = 101

  GO

  SELECT * FROM auditEmployeeData

  GO

  /*Updating the employee record for employee number 101 to change
both the bank account number and social security number (SSN) causes the
UPDATE trigger to fire and an audit trail to be produced.*/

  UPDATE employeeData

  SET emp_bankAccountNumber = ‘133146A0’, emp_SSN = ‘R-M53550M’

  WHERE emp_id = 101

  GO

  SELECT * FROM auditEmployeeData

  GO

  F. 使用 COLUMNS_UPDATED 测试 8 列以上

  如果必须测试影响到说明中前 8 列以外的排列的换代时,必须采取 UBSTRING
函数测试由 COLUMNS_UPDATED 返回的方便的号。下例测试影响
Northwind.dbo.Customers 表中的第 3、第 5 要么第 9 列的更新。

  USE Northwind

  DROP TRIGGER tr1

  GO

  CREATE TRIGGER tr1 ON Customers

  FOR UPDATE AS

  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 ‘Columns 3, 5 and 9 updated’

  GO

  UPDATE Customers

  SET ContactName=ContactName,

  Address=Address,

  Country=Country

  GO

发表评论

电子邮件地址不会被公开。 必填项已用*标注