喜忧参半的SQL Server触发器-开源中国

SQL Server触发器在非常有争议的主题。它们能以较低的成本提供便利，但经常被开发人员、DBA误用，导致性能瓶颈或维护性挑战。

本文简要回顾了触发器，并深入讨论了如何有效地使用触发器，以及何时触发器会使开发人员陷入难以逃脱的困境。

虽然本文中的所有演示都是在SQL Server中进行的，但这里提供的建议是大多数数据库通用的。触发器带来的挑战在MySQL、PostgreSQL、MongoDB和许多其他应用中也可以看到。

什么是触发器

可以在数据库或表上定义SQL Server触发器，它允许代码在发生特定操作时自动执行。本文主要关注表上的DML触发器，因为它们往往被过度使用。相反，数据库的DDL触发器通常更集中，对性能的危害更小。

触发器是对表中数据更改时进行计算的一组代码。触发器可以定义为在插入、更新、删除或这些操作的任何组合上执行。MERGE操作可以触发语句中每个操作的触发器。

触发器可以定义为INSTEAD OF或AFTER。AFTER触发器发生在数据写入表之后，是一组独立的操作，和写入表的操作在同一事务执行，但在写入发生之后执行。如果触发器失败，原始操作也会失败。INSTEAD OF触发器替换调用的写操作。插入、更新或删除操作永远不会发生，而是执行触发器的内容。

触发器允许在发生写操作时执行TSQL，而不管这些写操作的来源是什么。它们通常用于在希望确保执行写操作时运行关键操作，如日志记录、验证或其他DML。这很方便，写操作可以来自API、应用程序代码、发布脚本，或者内部流程，触发器无论如何都会触发。

触发器是什么样的

用WideWorldImporters示例数据库中的Sales.Orders 表举例，假设需要记录该表上的所有更新或删除操作，以及有关更改发生的一些细节。这个操作可以通过修改代码来完成，但是这样做需要对表的代码写入中的每个位置进行更改。通过触发器解决这一问题，可以采取以下步骤:

1. 创建一个日志表来接受写入的数据。下面的TSQL创建了一个简单日志表，以及一些添加的数据点：

CREATE TABLE Sales.Orders_log(  Orders_log_ID int NOT NULL IDENTITY(1,1)             CONSTRAINT PK_Sales_Orders_log PRIMARY KEY CLUSTERED,  OrderID int NOT NULL,  CustomerID_Old int NOT NULL,  CustomerID_New int NOT NULL,  SalespersonPersonID_Old int NOT NULL,  SalespersonPersonID_New int NOT NULL,  PickedByPersonID_Old int NULL,  PickedByPersonID_New int NULL,  ContactPersonID_Old int NOT NULL,  ContactPersonID_New int NOT NULL,  BackorderOrderID_Old int NULL,  BackorderOrderID_New int NULL,  OrderDate_Old date NOT NULL,  OrderDate_New date NOT NULL,  ExpectedDeliveryDate_Old date NOT NULL,  ExpectedDeliveryDate_New date NOT NULL,  CustomerPurchaseOrderNumber_Old nvarchar(20) NULL,  CustomerPurchaseOrderNumber_New nvarchar(20) NULL,  IsUndersupplyBackordered_Old bit NOT NULL,  IsUndersupplyBackordered_New bit NOT NULL,  Comments_Old nvarchar(max) NULL,  Comments_New nvarchar(max) NULL,  DeliveryInstructions_Old nvarchar(max) NULL,  DeliveryInstructions_New nvarchar(max) NULL,  InternalComments_Old nvarchar(max) NULL,  InternalComments_New nvarchar(max) NULL,  PickingCompletedWhen_Old datetime2(7) NULL,  PickingCompletedWhen_New datetime2(7) NULL,  LastEditedBy_Old int NOT NULL,  LastEditedBy_New int NOT NULL,  LastEditedWhen_Old datetime2(7) NOT NULL,  LastEditedWhen_New datetime2(7) NOT NULL,  ActionType VARCHAR(6) NOT NULL,  ActionTime DATETIME2(3) NOT NULL,UserName VARCHAR(128) NULL);

该表记录所有列的旧值和新值。这是非常全面的，我们可以简单地记录旧版本的行，并能够通过将新版本和旧版本合并在一起来了解更改的过程。最后3列是新增的，提供了有关执行的操作类型(插入、更新或删除)、时间和操作人。

2. 创建一个触发器来记录表的更改:

CREATE TRIGGER TR_Sales_Orders_Audit  ON Sales.Orders  AFTER INSERT, UPDATE, DELETEASBEGIN  SET NOCOUNT ON;  INSERT INTO Sales.Orders_log    (OrderID, CustomerID_Old, CustomerID_New,                  SalespersonPersonID_Old, SalespersonPersonID_New,                  PickedByPersonID_Old, PickedByPersonID_New,           ContactPersonID_Old, ContactPersonID_New,                  BackorderOrderID_Old, BackorderOrderID_New,                  OrderDate_Old, OrderDate_New, ExpectedDeliveryDate_Old,           ExpectedDeliveryDate_New,                  CustomerPurchaseOrderNumber_Old,                  CustomerPurchaseOrderNumber_New,                  IsUndersupplyBackordered_Old,                  IsUndersupplyBackordered_New,           Comments_Old, Comments_New,                  DeliveryInstructions_Old, DeliveryInstructions_New,                  InternalComments_Old, InternalComments_New,                  PickingCompletedWhen_Old,           PickingCompletedWhen_New, LastEditedBy_Old,                  LastEditedBy_New, LastEditedWhen_Old,                  LastEditedWhen_New, ActionType, ActionTime, UserName)  SELECT    ISNULL(Inserted.OrderID, Deleted.OrderID) AS OrderID,    Deleted.CustomerID AS CustomerID_Old,    Inserted.CustomerID AS CustomerID_New,    Deleted.SalespersonPersonID AS SalespersonPersonID_Old,    Inserted.SalespersonPersonID AS SalespersonPersonID_New,    Deleted.PickedByPersonID AS PickedByPersonID_Old,    Inserted.PickedByPersonID AS PickedByPersonID_New,    Deleted.ContactPersonID AS ContactPersonID_Old,    Inserted.ContactPersonID AS ContactPersonID_New,    Deleted.BackorderOrderID AS BackorderOrderID_Old,    Inserted.BackorderOrderID AS BackorderOrderID_New,    Deleted.OrderDate AS OrderDate_Old,    Inserted.OrderDate AS OrderDate_New,    Deleted.ExpectedDeliveryDate                           AS ExpectedDeliveryDate_Old,    Inserted.ExpectedDeliveryDate                           AS ExpectedDeliveryDate_New,    Deleted.CustomerPurchaseOrderNumber                           AS CustomerPurchaseOrderNumber_Old,    Inserted.CustomerPurchaseOrderNumber                           AS CustomerPurchaseOrderNumber_New,    Deleted.IsUndersupplyBackordered                           AS IsUndersupplyBackordered_Old,    Inserted.IsUndersupplyBackordered                           AS IsUndersupplyBackordered_New,    Deleted.Comments AS Comments_Old,    Inserted.Comments AS Comments_New,    Deleted.DeliveryInstructions                          AS DeliveryInstructions_Old,    Inserted.DeliveryInstructions                          AS DeliveryInstructions_New,    Deleted.InternalComments AS InternalComments_Old,    Inserted.InternalComments AS InternalComments_New,    Deleted.PickingCompletedWhen                         AS PickingCompletedWhen_Old,    Inserted.PickingCompletedWhen                         AS PickingCompletedWhen_New,    Deleted.LastEditedBy AS LastEditedBy_Old,    Inserted.LastEditedBy AS LastEditedBy_New,    Deleted.LastEditedWhen AS LastEditedWhen_Old,    Inserted.LastEditedWhen AS LastEditedWhen_New,    CASE      WHEN Inserted.OrderID IS NULL THEN 'DELETE'      WHEN Deleted.OrderID IS NULL THEN 'INSERT'      ELSE 'UPDATE'    END AS ActionType,    SYSUTCDATETIME() ActionTime,    SUSER_SNAME() AS UserName  FROM Inserted  FULL JOIN Deleted  ON Inserted.OrderID = Deleted.OrderID;END

该触发器的唯一功能是将数据插入到日志表中，每行数据对应一个给定的写操作。它很简单，随着时间的推移易于记录和维护，表也会发生变化。如果需要跟踪其他详细信息，可以添加其他列，如数据库名称、服务器名称、受影响列的行数或调用的应用程序。

3.最后一步是测试和验证日志表是否正确。

以下是添加触发器后对表进行更新的测试:

UPDATE Orders  SET InternalComments = 'Item is no longer backordered',    BackorderOrderID = NULL,    IsUndersupplyBackordered = 0,    LastEditedBy = 1,    LastEditedWhen = SYSUTCDATETIME()FROM sales.OrdersWHERE Orders.OrderID = 10;

结果如下：

上面省略了一些列，但是我们可以快速确认已经触发了更改，包括日志表末尾新增的列。

INSERT和DELETE

前面的示例中，进行插入和删除操作后，读取日志表中使用的数据。这种特殊的表可以作为任何相关写操作的一部分。INSERT将包含被插入操作触发，DELETE将被删除操作触发，UPDATE包含被插入和删除操作触发。

对于INSERT和UPDATE，将包含表中每个列新值的快照。对于DELETE和UPDATE操作，将包含写操作之前表中每个列旧值的快照。

触发器什么时候最有用

DML触发器的最佳使用是简短、简单且易于维护的写操作，这些操作在很大程度上独立于应用程序业务逻辑。

触发器的一些重要用途包括:
记录对历史表的更改
审计用户及其对敏感表的操作。
向表中添加应用程序可能无法使用的额外值(由于安全限制或其他限制)，例如:

登录/用户名
操作发生时间
服务器/数据库名称

简单的验证。

关键是让触发器代码保持足够的紧凑，从而便于维护。当触发器增长到成千上万行时，它们就成了开发人员不敢去打扰的黑盒。结果，更多的代码被添加进来，但是旧的代码很少被检查。即使有了文档，这也很难维护。

为了让触发器有效地发挥作用，应该将它们编写为基于设置的。如果存储过程必须在触发器中使用，则确保它们在需要时使用表值参数，以便可以基于集的方式移动数据。下面是一个触发器的示例，该触发器遍历id，以便使用结果顺序id执行示例存储过程:

CREATE TRIGGER TR_Sales_Orders_Process  ON Sales.Orders  AFTER INSERTASBEGIN  SET NOCOUNT ON;  DECLARE @count INT;  SELECT @count = COUNT(*) FROM inserted;  DECLARE @min_id INT;  SELECT @min_id = MIN(OrderID) FROM inserted;  DECLARE @current_id INT = @min_id;  WHILE @current_id @current_id + @count  BEGIN    EXEC dbo.process_order_fulfillment                         @OrderID = @current_id;    SELECT @current_id = @current_id + 1;  ENDEND

虽然相对简单，但当一次插入多行时对 Sales.Orders的INSERT操作的性能将受到影响，因为SQL Server在执行process_order_fulfillment存储过程时将被迫逐个执行。一个简单的修复方法是重写存储过程，并将一组Order id传递到存储过程中，而不是一次一个地这样做:

CREATE TYPE dbo.udt_OrderID_List AS TABLE(  OrderID INT NOT NULL,  PRIMARY KEY CLUSTERED (  OrderID ASC));GOCREATE TRIGGER TR_Sales_Orders_Process  ON Sales.Orders  AFTER INSERTASBEGIN  SET NOCOUNT ON;  DECLARE @OrderID_List dbo.udt_OrderID_List;  EXEC dbo.process_order_fulfillment @OrderIDs = @OrderID_List;END

更改的结果是将完整的id集合从触发器传递到存储过程并进行处理。只要存储过程以基于集合的方式管理这些数据，就可以避免重复执行，也就是说，避免在触发器内使用存储过程有很大的价值，因为它们添加了额外的封装层，进一步隐藏了在数据写入表时执行的TSQL。它们应该被认为是最后的手段，只有当可以在应用程序的许多地方多次重写TSQL时才使用。

什么时候触发器是危险的

架构师和开发人员面临的最大挑战之一是确保触发器只在需要时使用，而不允许它们成为一刀切的解决方案。向触发器添加TSQL通常被认为比向应用程序添加代码更快、更容易，但随着时间的推移，这样做的成本会随着每添加一行代码而增加。

触发器在以下情况下会变得危险:

保持尽可能少的触发以减少复杂性。
触发代码变得复杂。如果更新表中的一行导致要执行数千行添加的触发器代码，那么开发人员就很难完全理解数据写入表时会发生什么。更糟糕的是，当出现问题时，故障排除非常具有挑战性。
触发器跨服务器。这将网络操作引入到触发器中，可能导致在出现连接问题时写入速度变慢或失败。如果目标数据库是要维护的对象，那么即使是跨数据库触发器也会有问题。
触发器调用触发器。触发器中最令人痛苦的是，当插入一行时，写操作会导致75个表中有100个触发器要执行。在编写触发器代码时，确保触发器可以执行所有必要的逻辑，而不会触发更多触发器。额外的触发通常是不必要的。
递归触发器被设置为ON。这是一个默认设置为off的数据库级别设置。打开时，它允许触发器的内容调用相同的触发器。递归触发器会极大地损害性能，调试时也会非常混乱。通常，当一个触发器中的DML作为操作的一部分触发其他触发器时，使用递归触发器。
函数、存储过程或视图都在触发器中。在触发器中封装更多的业务逻辑会使它们变得更复杂，并给人一种触发器代码短小简单的错误印象，而实际上并非如此。尽可能避免在触发器中使用存储过程和函数。
迭代发生。循环和游标本质上是逐行操作的，可能会导致对1000行的操作一次触发1000次，这极大地损害了查询性能。

这是一个很长的列表，但通常可以总结为短而简单的触发器会表现得更好，并避免上面的大多数陷阱。如果使用触发器来维护复杂的业务逻辑，那么随着时间的推移，越来越多的业务逻辑将被添加进来，并且不可避免地将违反上述最佳实践。

重要的是要注意，为了维护原子的、事务，受触发器影响的任何对象都将保持事务处于打开状态，直到该触发器完成。这意味着长触发器不仅会使事务持续时间更长，而且还会持有锁并导致持续时间更长。因此，在测试触发器时，在为现有触发器创建或添加额外逻辑时，应该了解它们对锁、阻塞和等待的影响。

如何改善触发器

有很多方法可以使触发器更易于维护、更容易理解和性能更高。以下是一些关于如何有效管理触发器和避免落入陷阱的建议。

触发器本身应该有良好的文档记录:

这个触发器为什么存在?
它能做什么?
它是如何工作的?
对于触发器的工作方式是否有任何例外或警告?

此外，如果触发器中的TSQL难以理解，那么可以添加内联注释，以帮助第一次查看它的开发人员。

下面是触发器文档的样例:

/*  12/29/2020 EHP  This trigger logs all changes to the table to the Orders_log        table that occur for non-internal customers.  CustomerID = -1 signifies an internal/test customer and         these are not audited.*/CREATE TRIGGER TR_Sales_Orders_Audit  ON Sales.Orders  FOR INSERT, UPDATE, DELETEASBEGIN  SET NOCOUNT ON;  INSERT INTO Sales.Orders_log    (OrderID, CustomerID_Old, CustomerID_New,                  SalespersonPersonID_Old, SalespersonPersonID_New,                 PickedByPersonID_Old, PickedByPersonID_New,           ContactPersonID_Old, ContactPersonID_New,                  BackorderOrderID_Old, BackorderOrderID_New,                  OrderDate_Old, OrderDate_New,                  ExpectedDeliveryDate_Old,           ExpectedDeliveryDate_New,                  CustomerPurchaseOrderNumber_Old,                  CustomerPurchaseOrderNumber_New,                  IsUndersupplyBackordered_Old,                  IsUndersupplyBackordered_New,           Comments_Old, Comments_New,                  DeliveryInstructions_Old, DeliveryInstructions_New,                  nternalComments_Old, InternalComments_New,                  PickingCompletedWhen_Old, PickingCompletedWhen_New,                  LastEditedBy_Old, LastEditedBy_New,                  LastEditedWhen_Old, LastEditedWhen_New,                  ActionType, ActionTime, UserName)  SELECT    ISNULL(Inserted.OrderID, Deleted.OrderID) AS OrderID,                  -- The OrderID can never change.                   --This ensures we get the ID correctly,                  --regardless of operation type.    Deleted.CustomerID AS CustomerID_Old,    Inserted.CustomerID AS CustomerID_New,    Deleted.SalespersonPersonID AS SalespersonPersonID_Old,    Inserted.SalespersonPersonID AS SalespersonPersonID_New,    Deleted.PickedByPersonID AS PickedByPersonID_Old,    Inserted.PickedByPersonID AS PickedByPersonID_New,    Deleted.ContactPersonID AS ContactPersonID_Old,    Inserted.ContactPersonID AS ContactPersonID_New,    Deleted.BackorderOrderID AS BackorderOrderID_Old,    Inserted.BackorderOrderID AS BackorderOrderID_New,    Deleted.OrderDate AS OrderDate_Old,    Inserted.OrderDate AS OrderDate_New,    Deleted.ExpectedDeliveryDate AS ExpectedDeliveryDate_Old,    Inserted.ExpectedDeliveryDate AS ExpectedDeliveryDate_New,    Deleted.CustomerPurchaseOrderNumber                     AS CustomerPurchaseOrderNumber_Old,    Inserted.CustomerPurchaseOrderNumber                     AS CustomerPurchaseOrderNumber_New,    Deleted.IsUndersupplyBackordered                     AS IsUndersupplyBackordered_Old,    Inserted.IsUndersupplyBackordered                     AS IsUndersupplyBackordered_New,    Deleted.Comments AS Comments_Old,    Inserted.Comments AS Comments_New,    Deleted.DeliveryInstructions                     AS DeliveryInstructions_Old,    Inserted.DeliveryInstructions                     AS DeliveryInstructions_New,    Deleted.InternalComments AS InternalComments_Old,    Inserted.InternalComments

喜忧参半的SQL Server触发器

参与评论

评论留言

还没有评论留言，赶紧来抢楼吧~~

给这篇文章打个标签吧~