参考资料：DML 触发器

最新推荐文章于 2019-09-03 22:58:36 发布

成都慢生活

最新推荐文章于 2019-09-03 22:58:36 发布

阅读量980

点赞数

分类专栏：技术文章标签： insert sql server delete 数据库 triggers schema

技术专栏收录该内容

254 篇文章 0 订阅

订阅专栏

参考资料：DML 触发器

文章标签

l DML 触发器

DML 触发器是当数据库服务器中发生数据操作语言 (DML) 事件时要执行的操作。DML 事件包括对表或视图发出的 UPDATE、INSERT 或 DELETE 语句。DML 触发器用于在数据被修改时强制执行业务规则，以及扩展 Microsoft SQL Server 2005 约束、默认值和规则的完整性检查逻辑。

本节提供了解、设计和实现 DML 触发器所需的信息。

主题	说明
了解 DML触发器	解释 DML 触发器的概念，并举例说明在什么情况下实现 DML 触发器。
设计 DML触发器	提供在设计 DML 触发器时应该考虑的特定指导原则、规则和限制。
实现 DML触发器	说明创建、修改或删除 DML 触发器的过程。

l 了解DML触发器

Microsoft SQL Server 2005 提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。触发器是一种特殊的存储过程，它在执行语言事件执行时自动生效。SQL Server 包括两大类触发器：DML 触发器和 DDL 触发器。

DDL 触发器是 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用这些触发器。

当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

DML 触发器在以下方面非常有用：

DML 触发器可通过数据库中的相关表实现级联更改。不过，通过级联引用完整性约束可以更有效地进行这些更改。

DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。

与 CHECK 约束不同，DML 触发器可以引用其他表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其他操作，如修改数据或显示用户定义错误信息。

DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。

一个表中的多个同类 DML 触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的操作来响应同一个修改语句。

l 设计DML触发器

设计 DML 触发器时要考虑的因素。

主题	说明
DML 触发器计划指南	介绍设计 AFTER 触发器和 INSTEAD OF 触发器时可以使用的不同方式。
指定 DML 触发器何时激发	通过示例说明 AFTER 触发器和 INSTEAD OF 触发器的触发方式之间的差别。
DML 触发器执行	说明 AFTER 触发器和 INSTEAD OF 触发器在触发之后如何执行。
设计 INSTEAD OF 触发器	介绍特定于 INSTEAD OF 触发器的设计指南。
管理触发器安全性	说明触发器代码如何在升级后的权限下运行，以及如何减轻此威胁。

DML 触发器计划指南

Microsoft SQL Server 2005 提供了两个用于设计 DML 触发器的选项：

执行 INSTEAD OF 触发器代替通常的触发操作。还可以对带有一个或多个基表的视图定义 INSTEAD OF 触发器，这些触发器可以扩展视图可支持的更新类型。

在执行 INSERT、UPDATE 或 DELETE 语句操作之后执行 AFTER 触发器。指定 AFTER 与指定 FOR 相同，后者是 SQL Server 早期版本中唯一可用的选项。AFTER 触发器只能在表上指定。

下表对 AFTER 触发器和 INSTEAD OF 触发器的功能进行了比较。

函数	AFTER 触发器	INSTEAD OF 触发器
适用范围	表	表和视图
每个表或视图包含触发器的数量	每个触发操作（UPDATE、DELETE 和 INSERT）包含多个触发器	每个触发操作（UPDATE、DELETE 和 INSERT）包含一个触发器
级联引用	无任何限制条件	不允许在作为级联引用完整性约束目标的表上使用 INSTEAD OF UPDATE 和 DELETE 触发器。
执行	晚于：约束处理声明性引用操作创建插入的和删除的表触发操作	早于: 约束处理替代：触发操作晚于：创建插入的和删除的表
执行顺序	可指定第一个和最后一个执行	不适用
插入的和删除的表中的 varchar(max)、nvarchar(max) 和 varbinary(max) 列引用。	允许	允许
插入的和删除的表中的 text、ntext 和 image 列引用。	不允许	允许

指定 DML 触发器何时激发

可通过指定以下两个选项之一来控制 DML 触发器何时激发：

AFTER 触发器将在处理触发操作（INSERT、UPDATE 或 DELETE）、INSTEAD OF 触发器和约束之后激发。可通过指定 AFTER 或 FOR 关键字来请求 AFTER 触发器。因为 FOR 关键字与 AFTER 效果相同，所以带有 FOR 关键字的 DML 触发器也归类为 AFTER 触发器。

INSTEAD OF 将在处理约束前激发，以替代触发操作。如果表有 AFTER 触发器，它们将在处理约束之后激发。如果违反了约束，将回滚 INSTEAD OF 触发器操作并且不执行 AFTER 触发器。

每个表或视图针对每个触发操作（UPDATE、DELETE 和 INSERT）可有一个相应的 INSTEAD OF 触发器。而一个表针对每个触发操作可有多个相应的 AFTER 触发器。

示例

A. 使用 INSTEAD OF 触发器替换标准触发操作

CREATE TRIGGER TableAInsertTrig ON TableA

INSTEAD OF INSERT

AS ...

B. 使用 AFTER 触发器增加标准触发操作

CREATE TRIGGER TableBDeleteTrig ON TableB

AFTER DELETE

AS ...

C. 使用 FOR 触发器增加标准触发操作

-- This statement uses the FOR keyword to generate an AFTER trigger.

CREATE TRIGGER TableCUpdateTrig ON TableC

FOR UPDATE

AS ...

DML 触发器执行

如果违反了约束，则永远不会执行 AFTER 触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。

执行 INSTEAD OF 触发器，而不执行触发操作。在创建插入的和删除的表（反映对基表所做的更改）之后，而在执行任何其他操作之前执行这些触发器。这些触发器在执行任何约束前执行，因此可执行预处理来补充约束操作。

为表定义的 INSTEAD OF 触发器对此表执行一条通常会再次激发该触发器的语句时，不会递归调用该触发器，而是如同表中没有 INSTEAD OF 触发器那样处理该语句，该语句将启动一系列约束操作和 AFTER 触发器执行。例如，如果 DML 触发器定义为表的 INSTEAD OF INSERT 触发器且该触发器对同一个表执行 INSERT 语句，则 INSTEAD OF 触发器执行的 INSERT 语句不会再次调用该触发器。该触发器执行的 INSERT 将启动用于执行约束操作的进程和触发为该表定义的所有 AFTER INSERT 触发器的进程。

为视图定义的 INSTEAD OF 触发器对该视图执行一条通常会再次激发 INSTEAD OF 触发器的语句时，不会递归调用该触发器，而是将语句解析为对该视图所依存的基表进行修改。在这种情况下，视图定义必须满足可更新视图的所有约束。有关可更新视图的定义，请参阅通过视图修改数据。例如，如果 DML 触发器定义为视图的 INSTEAD OF UPDATE 触发器且该触发器执行引用同一视图的 UPDATE 语句，则 INSTEAD OF 触发器执行的 UPDATE 语句不会再次调用该触发器，而是如同该视图没有 INSTEAD OF 触发器那样在视图中处理该触发器执行的 UPDATE 语句。必须将 UPDATE 更改的列解析为一个基表。对基表的每次修改都将应用约束并触发为该表定义的 AFTER 触发器。

DML 触发器的性能开销通常很低。运行 DML 触发器所花的时间大部分都用于引用其他表，这些表可能位于内存中，也可能位于数据库设备上。删除的和插入的表始终位于内存中。触发器所引用的其他表的位置将确定操作所需的时间。

注意：建议不要在 DML 触发器中使用游标，因为这可能对性能产生负面影响。使用基于行集的逻辑（而非游标）可以设计影响多行的 DML 触发器。

设计 INSTEAD OF 触发器

INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

INSTEAD OF 触发器可以进行以下操作：

忽略批处理中的某些部分。

不处理批处理中的某些部分并记录有问题的行。

如果遇到错误情况则采取备用操作。

注意：对于含有使用 DELETE 或 UPDATE 级联操作定义的外键的表，不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。

将此逻辑作为 INSTEAD OF 触发器的一部分进行编码，可避免所有访问数据的应用程序必须重新实现该逻辑。

示例

在下列 Transact-SQL 语句序列中，INSTEAD OF 触发器更新视图中的两个基表。另外，显示以下处理错误的方法：

忽略对 Person 表的重复插入，并且插入的信息将记录在 PersonDuplicates 表中。

将对 EmployeeTable 的重复插入转变为 UPDATE 语句，该语句将当前信息检索至 EmployeeTable，而不会产生重复键冲突。

Transact-SQL 语句创建两个基表、一个视图、一个记录错误表和视图上的 INSTEAD OF 触发器。以下表将个人数据和业务数据分开并且是视图的基表。

CREATE TABLE Person

(

SSN char(11) PRIMARY KEY,

Name nvarchar(100),

Address nvarchar(100),

Birthdate datetime

)

CREATE TABLE EmployeeTable

(

EmployeeID int PRIMARY KEY,

SSN char(11) UNIQUE,

Department nvarchar(10),

Salary money,

CONSTRAINT FKEmpPer FOREIGN KEY (SSN)

REFERENCES Person (SSN)

)

下面的视图使用某个人的两个表中的所有相关数据建立报表。

CREATE VIEW Employee AS

SELECT P.SSN as SSN, Name, Address,

Birthdate, EmployeeID, Department, Salary

FROM Person P, EmployeeTable E

WHERE P.SSN = E.SSN

可记录对插入具有重复的社会保障号的行的尝试。PersonDuplicates 表记录插入的值、尝试插入的用户的姓名和插入的时间。

CREATE TABLE PersonDuplicates

(

SSN char(11),

Name nvarchar(100),

Address nvarchar(100),

Birthdate datetime,

InsertSNAME nchar(100),

WhenInserted datetime

)

INSTEAD OF 触发器将行插入到单个视图的多个基表中。在 PersonDuplicates 表中记录了插入具有重复社会保障号的行的尝试。EmployeeTable 中的重复行将更改为更新语句。

CREATE TRIGGER IO_Trig_INS_Employee ON Employee

INSTEAD OF INSERT

BEGIN

SET NOCOUNT ON

-- Check for duplicate Person. If there is no duplicate, do an insert.

IF (NOT EXISTS (SELECT P.SSN

FROM Person P, inserted I

WHERE P.SSN = I.SSN))

INSERT INTO Person

SELECT SSN,Name,Address,Birthdate

FROM inserted

ELSE

-- Log an attempt to insert duplicate Person row in PersonDuplicates table.

INSERT INTO PersonDuplicates

SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()

FROM inserted

-- Check for duplicate Employee. If no there is duplicate, do an INSERT.

IF (NOT EXISTS (SELECT E.SSN

FROM EmployeeTable E, inserted

WHERE E.SSN = inserted.SSN))

INSERT INTO EmployeeTable

SELECT EmployeeID,SSN, Department, Salary

FROM inserted

ELSE

--If there is a duplicate, change to UPDATE so that there will not

--be a duplicate key violation error.

UPDATE EmployeeTable

SET EmployeeID = I.EmployeeID,

Department = I.Department,

Salary = I.Salary

FROM EmployeeTable E, inserted I

WHERE E.SSN = I.SSN

END

管理触发器安全性

默认情况下，在调用触发器的用户的上下文中执行 DML 和 DDL 触发器。触发器的调用方是执行使触发器运行的语句的用户。例如，如果用户 Mary 执行可以使 DML 触发器 DML_trigMary 运行的 DELETE 语句，则 DML_trigMary 中的代码将在 Mary 的用户特权上下文中执行。希望向数据库或服务器实例中引入恶意代码的用户可以使用此默认行为。例如，下面由用户 JohnDoe 创建的 DDL 触发器：

CREATE TRIGGER DDL_trigJohnDoe

ON DATABASE

FOR ALTER_TABLE

GRANT CONTROL SERVER TO JohnDoe ;

此触发器的作用是在具有执行 GRANT CONTROL SERVER 语句的权限的用户（如 sysadmin 固定服务器角色的成员）执行 ALTER TABLE 语句时，立即授予 JohnDoe CONTROL SERVER 权限。也就是说，虽然 JohnDoe 无法给自己授予 CONTROL SERVER 权限，但他启用了触发器代码，向自己授予在升级的特权下执行的权限。对于此类安全隐患，DML 和 DDL 触发器都处于打开状态。

保证触发器安全的最佳方法

可以采取下列措施阻止触发器代码在升级特权下执行：

注意数据库和服务器实例中存在的 DML 和 DDL 触发器，方法是查询 sys.triggers 和 sys.server_triggers 目录视图。下面的查询将返回当前数据库中的所有 DML 触发器和数据库级别的 DDL 触发器，以及服务器实例中所有服务器级别的 DDL 触发器：

SELECT type, name, parent_class_desc FROM sys.triggers

UNION

SELECT type, name, parent_class_desc FROM sys.server_triggers ;

使用 DISABLE TRIGGER 禁用在升级特权下执行时可能会损害数据库或服务器完整性的触发器。下面的语句可以禁用当前数据库中所有数据库级别的 DDL 触发器：

DISABLE TRIGGER ALL ON DATABASE

下面的语句可以禁用服务器实例中所有服务器级别的 DDL 触发器：

DISABLE TRIGGER ALL ON ALL SERVER

下面的语句可以禁用当前数据库中的所有 DML 触发器：

DECLARE @schema_name sysname, @trigger_name sysname, @object_name sysname ;

DECLARE @sql nvarchar(max) ;

DECLARE trig_cur CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT SCHEMA_NAME(schema_id) AS schema_name,

name AS trigger_name,

OBJECT_NAME(parent_object_id) as object_name

FROM sys.objects WHERE type in ('TR', 'TA') ;

OPEN trig_cur ;

FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ;

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @sql = 'DISABLE TRIGGER ' + @schema_name + '.' + @trigger_name +

' ON ' + @schema_name + '.' + @object_name + ' ; ' ;

EXEC (@sql) ;

FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ;

END

-- Verify triggers are disabled. Should return an empty result set.

SELECT * FROM sys.triggers WHERE is_disabled = 0 ;

CLOSE trig_cur ;

DEALLOCATE trig_cur;

l 实现DML触发器

实现 DML 触发器

创建 DML 触发器前应考虑下列问题：

CREATE TRIGGER 语句必须是批处理中的第一个语句，该语句后面的所有其他语句被解释为 CREATE TRIGGER 语句定义的一部分。

创建 DML 触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。

DML 触发器为数据库对象，其名称必须遵循标识符的命名规则。

虽然 DML 触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建 DML 触发器。

虽然 DML 触发器可以引用临时表，但不能对临时表或系统表创建 DML 触发器。不应引用系统表，而应使用信息架构视图。

对于含有用 DELETE 或 UPDATE 操作定义的外键的表，不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。

虽然 TRUNCATE TABLE 语句类似于不带 WHERE 子句的 DELETE 语句（用于删除所有行），但它并不会触发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。

WRITETEXT 语句不会触发 INSERT 或 UPDATE 触发器。

重要事项：未来版本的 SQL Server 中会删除从触发器返回结果集的功能。返回结果集的触发器可能会引起应用程序出现并非计划中与它们协同工作的意外行为。请避免在新的开发工作中从触发器返回结果集，并考虑修改目前具有此行为的应用程序。若要在 SQL Server 2005 中防止从触发器返回结果集，请将disallow results from triggers 选项设置为 1。在未来版本的 SQL Server 中，此选项的默认设置将为 1。

创建 DML 触发器时需指定：

名称。

定义触发器时所基于的表。

触发器被触发的时间。

激活触发器的数据修改语句。有效选项为 INSERT、UPDATE 或 DELETE。多个数据修改语句可激活同一个触发器。例如，触发器可由 INSERT 或 UPDATE 语句激活。

执行触发器操作的编程语句。

多个 DML 触发器

一个表中可以具有多个给定类型的 AFTER 触发器，只要它们的名称不相同；每个触发器可以执行多个函数。但是，每个触发器只能应用于一个表，尽管一个触发器可以应用于三个用户操作（UPDATE、INSERT 和 DELETE）的任何子集。

一个表只能具有一个给定类型的 INSTEAD OF 触发器。

触发器权限和所有权

用定义触发器时所基于的表或视图的名称架构创建触发器。例如，如果触发器 Trigger1 是对 HumanResources.Employee 表创建的，则触发器的架构限定的名称为 Employee.Trigger1。

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