65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 数据库触发器概述

Md. Marufuzzaman
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.71/5 (41投票s)

2009年8月7日

CPOL

6分钟阅读

viewsIcon

338944

downloadIcon

1487

SQL Server 数据库触发器概述

目录

概述

触发器最适合用于实现业务规则以及执行数据验证或数据修改，当其他方法不足以满足需求时，它们是最佳选择。触发器通常用于两个方面：创建审计记录并反映关键业务表的变化，以及根据 T-SQL 中编码的业务规则集来验证更改。

在本文中，我将演示如何创建触发器、触发器的用途、不同类型的触发器以及性能注意事项。

什么是触发器？

数据库触发器是一个存储过程，它在预定义事件发生时自动调用。数据库触发器使 DBA（数据库管理员）能够在独立数据库之间创建额外的关系。换句话说，可以定义一个触发器，在执行 INSERT、UPDATE 或 DELETE 操作之前或之后执行，并且可以每修改一行执行一次，或者每条 SQL 语句执行一次。如果发生触发器事件，将在适当的时间调用触发器的函数来处理该事件。

触发器可以分配给表或视图。但是，尽管有两种类型的触发器（INSTEAD OF 和 AFTER），但只能有一种类型的触发器分配给视图。INSTEAD OF 触发器通常与视图相关联，并在对该视图执行 UPDATE 操作时运行。AFTER 触发器在修改操作发生后触发。
从性能角度来看，触发器越少越好，因为我们会调用更少的进程。因此，不要认为为使事情模块化而为每个操作设置一个触发器不会导致性能下降。触发器的主要开销是引用触发器中存在的两个专用表——deleted 和 inserted，或者用于业务规则的其他表。模块化触发器以使整个过程更易于理解会多次引用这些表，从而导致更大的开销。

注意：无法创建触发器来在两个或多个表中的数据修改发生时触发。触发器只能与单个表关联。

为什么要使用触发器？

触发器可用于提高数据完整性。当对数据执行操作时，可以检查数据的操作是否符合底层业务规则，从而避免在表中出现错误的条目。例如：

  • 当订单总额超过 1000 美元时，我们可能希望向客户免费发送一件商品。将创建一个触发器，在订单完成后检查订单总额，以确定是否需要插入额外的订单行。
  • 在银行场景中，当请求从 ATM 机提取现金时，存储过程将在客户的对账单表中为此次提款创建一个记录，并且触发器将自动按需减少余额。触发器也可能是检查客户余额以验证是否有足够余额以允许提款的地方。通过在对账单表上设置触发器，我们可以确信任何创建的对账单条目（无论是提款还是存款）都将在一个中心位置进行验证和处理。

注意：此处我们仅讨论数据完整性，而不讨论参照完整性。

触发器的另一个用途是在满足特定标准时执行操作。一个例子是当库存水平达到预设水平时，会发送电子邮件请求运送更多商品，或者将订单置于待处理状态。但是，如果我们从 trigger 中 insert 数据到另一个表中，我们必须小心，以确保我们插入的表没有会导致第一个触发器触发的触发器。有可能编写导致无限循环的触发器，因为我们可以定义一个针对 **TableA** 的 trigger，它插入到 **TableB**，然后为 **TableB** 定义一个 trigger，它更新 **TableA**。这种情况最终会导致 SQL Server 生成错误。以下图表将演示这一点：

Diagram

图 1
  1. 存储过程 A 更新 TableA。
  2. 这会触发 TableA 中的一个触发器。
  3. 为 TableA 定义的触发器更新 TableB。
  4. TableB 有一个触发器会被触发。
  5. 来自 TableB 的此触发器更新 TableA。

创建和使用触发器  

触发器是一种特殊的存储过程，当数据库服务器中的事件发生时会自动执行。DML 触发器在用户尝试通过数据操作语言 (DML) 事件修改数据时执行。DML 事件是表或视图上的 INSERT、UPDATE 或 DELETE 语句。更多详细信息可在此链接中找到。

CREATE TRIGGER 语句

CREATE TRIGGER 语句在数据库中定义一个触发器。

调用

此语句可以嵌入应用程序程序中，或通过动态 SQL 语句发出。它是一个可执行语句，只有当包处于 DYNAMICRULES 运行行为（SQLSTATE 42509）时，才能动态准备它。

授权

语句的授权 ID 所拥有的特权必须至少包括以下一项：

  • 定义 BEFORE 或 AFTER 触发器的表的 ALTER 特权
  • 定义 INSTEAD OF 触发器的视图的 CONTROL 特权
  • 定义 INSTEAD OF 触发器的视图的定义者
  • 定义触发器的表或视图的模式的 ALTERIN 特权
  • SYSADM 或 DBADM 权限，以及以下一项：
    • 数据库的 IMPLICIT_SCHEMA 权限，如果触发器的隐式或显式模式名称不存在
    • 模式的 CREATEIN 特权，如果触发器的模式名称引用现有模式

如果触发器定义者仅因为拥有 SYSADM 权限而能够创建触发器，则该定义者将被授予显式 DBADM 权限以用于创建触发器。

触发器语法 

CREATE TRIGGER name ON table
   [WITH ENCRYPTION]
   [FOR/AFTER/INSTEAD OF]
   [INSERT, UPDATE, DELETE]
   [NOT FOR REPLICATION]
AS
BEGIN
--SQL statements
...
END

示例  

我们来看一个例子。我们有一个包含某些列的表。我们的目标是创建一个 TRIGGER，它将在每个列的每次数据修改时触发，并跟踪该列的修改次数。示例如下：

-- ==========================================================
-- Author: Md. Marufuzzaman
-- Create date: 
-- Description: Alter count for any modification  
-- ===========================================================
CREATE TRIGGER [TRIGGER_ALTER_COUNT] ON [dbo].[tblTriggerExample] 
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @TransID  VARCHAR(36)
 SELECT @TransID = TransactionID FROM INSERTED
 UPDATE [dbo].[tblTriggerExample] SET AlterCount = AlterCount + 1 
          ,LastUpdate = GETDATE()
    WHERE TransactionID = @TransID
END

SQL Table

图 2 (SQL 表)

触发器类型

触发器在 INSERT、DELETE 或 UPDATE 操作上触发时，有三种主要类型。与存储过程一样，这些触发器也可以加密以增加安全性。更多详细信息可在此链接中找到。

最佳实践

最重要的一点是使触发器尽可能简短，以便快速执行，就像存储过程一样。触发器触发所需的时间越长，锁在底层表上持有的时间就越长。为此，我们可以在触发器中放置游标，但最佳实践规定我们不这样做。游标不是数据库中最快的对象，如果我们觉得需要游标，应该重新审视问题并寻找不同的解决方案。一种解决办法可能是执行两次，甚至三次更新，或者改用临时表。

使用触发器来强制执行业务规则，或完成对组织有积极影响的操作，或者在某个操作可以阻止系统出现问题时执行。例如，创建一个触发器，当订单即将发货时，该触发器会向客户发送电子邮件，提供订单详情等。

注意：在需要时，使用 @@ROWCOUNT 来检查受影响的行数。

结论

我希望本文对您有所帮助。 祝您愉快！

历史

  • 2009 年 8 月 7 日：首次发布
© . All rights reserved.