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 表更改时,下例将电子邮件发送给指定的人员 (玛丽M)。

  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 重临的适用的位。下例测试影响
诺思wind.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

发表评论

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