SQL开发知识：关于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);