SQL Server 开发系列（第七期）：触发器与约束——数据完整性的守护者

绩隐金
0 阅读10分钟

SQL Server 开发系列（第七期）：触发器与约束——数据完整性的守护者

上一期我们学习了存储过程与函数。这一期，我们进入数据完整性保护的最后一公里——触发器和约束。你是否遇到过：业务逻辑分散在多个应用导致数据不一致？想记录数据变更历史却无处下手？需要实现复杂的跨表验证却不知道用什么工具？约束和触发器是数据库层面的守护者，把数据完整性规则固化在数据库内，比依赖应用层更可靠。

一、约束 vs 触发器：核心区别

维度约束触发器
执行时机语句执行期间语句执行前后
性能快（元数据级别）慢（额外操作）
跨表验证❌ 不支持✅ 支持
复杂逻辑❌ 仅简单检查✅ 任意 T-SQL
审计日志❌ 不支持✅ 支持
业务规则基本完整性复杂业务逻辑
推荐度✅ 优先使用⚠️ 最后手段

黄金法则：能用约束解决的，绝不用触发器。

-- 约束示例（声明式）
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    Amount DECIMAL CHECK (Amount >= 0),  -- 检查约束
    OrderDate DATETIME2 DEFAULT GETDATE(),
    Status VARCHAR(20) DEFAULT 'Pending',
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- 触发器示例（过程式，当约束无法满足时使用）
CREATE TRIGGER trg_Orders_Audit
ON Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO AuditLog (TableName, Action, OldData, NewData, ChangeTime)
    SELECT 'Orders', 'UPDATE', ..., GETDATE()
    FROM inserted, deleted
END

二、五种约束详解

2.1 主键约束（PRIMARY KEY）

-- 单列主键
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100)
);

-- 复合主键
CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

-- 事后添加
ALTER TABLE Products ADD CONSTRAINT PK_Products PRIMARY KEY (ProductID);

性能要点：

  • 主键默认创建聚集索引（除非指定 NONCLUSTERED）
  • 主键值不应更新（否则所有非聚集索引都要更新）

2.2 外键约束（FOREIGN KEY）

-- 基础外键
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID)
);

-- 级联操作
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
        ON DELETE CASCADE     -- 删除客户时级联删除订单
        ON UPDATE CASCADE     -- 更新客户ID时级联更新订单
);

-- 自引用外键（员工-经理关系）
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    ManagerID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

级联操作选项：

选项行为适用场景
NO ACTION拒绝操作（默认）防止孤儿数据
CASCADE级联更新/删除主子表强依赖
SET NULL外键列设为 NULL关系可选
SET DEFAULT设为默认值关系降级

2.3 唯一约束（UNIQUE）

-- 单列唯一
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20)
);

-- 复合唯一
CREATE TABLE UserRoles (
    UserID INT,
    RoleID INT,
    CONSTRAINT UQ_UserRoles UNIQUE (UserID, RoleID)
);

-- 为 Phone 列创建唯一约束，但允许多个 NULL
CREATE UNIQUE NONCLUSTERED INDEX UQ_Users_Phone 
ON Users(Phone) WHERE Phone IS NOT NULL;  -- SQL Server 2008+ 筛选唯一索引

唯一约束 vs 唯一索引：

  • 唯一约束 = 唯一索引 + 元数据标识
  • 性能相同，但唯一约束更符合声明式设计

2.4 检查约束（CHECK）

-- 范围检查
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL CHECK (Price >= 0),
    Quantity INT CHECK (Quantity BETWEEN 0 AND 10000),
    Status VARCHAR(20) CHECK (Status IN ('Active', 'Inactive', 'Discontinued'))
);

-- 跨列检查
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Discount DECIMAL,
    Amount DECIMAL,
    CONSTRAINT CHK_Discount CHECK (Discount <= Amount * 0.5)
);

-- 复杂表达式
CREATE TABLE Persons (
    BirthDate DATE,
    CHECK (DATEDIFF(YEAR, BirthDate, GETDATE()) BETWEEN 0 AND 120)
);

2.5 默认约束（DEFAULT）

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME2 DEFAULT GETDATE(),
    Status VARCHAR(20) DEFAULT 'Pending',
    Amount DECIMAL DEFAULT 0,
    CreateUser VARCHAR(50) DEFAULT SUSER_NAME()
);

三、触发器深度解析

3.1 AFTER 触发器（最常用）

CREATE TRIGGER trg_Orders_AfterUpdate
ON Orders
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 检查是否真的有数据变化
    IF @@ROWCOUNT = 0 RETURN;
    
    -- 使用 inserted 和 deleted 虚拟表
    INSERT INTO AuditLog (TableName, Action, OrderID, OldAmount, NewAmount, ChangeTime)
    SELECT 
        'Orders',
        'UPDATE',
        COALESCE(i.OrderID, d.OrderID),
        d.Amount,
        i.Amount,
        GETDATE()
    FROM inserted i
    FULL JOIN deleted d ON i.OrderID = d.OrderID
    WHERE ISNULL(i.Amount, 0) <> ISNULL(d.Amount, 0);  -- 只记录实际变化的行
END

触发器的虚拟表：

操作inserteddeleted
INSERT新行空
UPDATE新值旧值
DELETE空删除的行

3.2 INSTEAD OF 触发器

-- 在视图上实现 INSERT（视图涉及多表）
CREATE TRIGGER trg_OrderView_Insert
ON OrderView
INSTEAD OF INSERT
AS
BEGIN
    -- 插入主表
    INSERT INTO Orders (OrderDate, CustomerID)
    SELECT OrderDate, CustomerID FROM inserted;
    
    -- 插入明细表
    INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
    SELECT SCOPE_IDENTITY(), ProductID, Quantity FROM inserted;
END

-- 阻止特定时间的删除
CREATE TRIGGER trg_Orders_InsteadOfDelete
ON Orders
INSTEAD OF DELETE
AS
BEGIN
    IF DATEPART(HOUR, GETDATE()) BETWEEN 22 AND 23
    BEGIN
        RAISERROR('22-23点禁止删除订单', 16, 1);
        RETURN;
    END
    
    -- 允许删除
    DELETE FROM Orders WHERE OrderID IN (SELECT OrderID FROM deleted);
END

3.3 DDL 触发器（数据库级、服务器级）

-- 禁止删除表
CREATE TRIGGER trg_PreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT '禁止删除表！';
    ROLLBACK;
END

-- 审计 DDL 操作
CREATE TRIGGER trg_AuditDDL
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    INSERT INTO DDLAudit (EventType, ObjectName, SQLText, LoginName, EventTime)
    VALUES (
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
        EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(255)'),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'VARCHAR(MAX)'),
        SUSER_NAME(),
        GETDATE()
    );
END

3.4 触发器的重要设置

-- 查看触发器选项
SELECT 
    name,
    is_instead_of_trigger,
    is_disabled,
    is_not_for_replication
FROM sys.triggers
WHERE parent_id = OBJECT_ID('Orders');

-- 禁用/启用触发器
DISABLE TRIGGER trg_Orders_Audit ON Orders;
ENABLE TRIGGER trg_Orders_Audit ON Orders;

-- 设置嵌套触发器（服务器级别）
EXEC sp_configure 'nested triggers', 0;  -- 0=禁止嵌套，1=允许（默认）
RECONFIGURE;

-- 设置递归触发器（数据库级别）
ALTER DATABASE YourDB SET RECURSIVE_TRIGGERS ON;

四、实战场景

4.1 审计日志（最常用）

-- 完整的审计触发器模板
CREATE TABLE AuditLog (
    AuditID BIGINT IDENTITY PRIMARY KEY,
    TableName VARCHAR(100),
    Action VARCHAR(10),
    RecordID VARCHAR(100),
    OldData NVARCHAR(MAX),
    NewData NVARCHAR(MAX),
    ChangeUser VARCHAR(100),
    ChangeTime DATETIME2,
    HostName VARCHAR(100),
    AppName VARCHAR(100)
);

CREATE TRIGGER trg_Products_Audit
ON Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Action VARCHAR(10);
    DECLARE @OldData NVARCHAR(MAX);
    DECLARE @NewData NVARCHAR(MAX);
    DECLARE @RecordID VARCHAR(100);
    
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        SET @Action = 'UPDATE';
    ELSE IF EXISTS (SELECT * FROM inserted)
        SET @Action = 'INSERT';
    ELSE
        SET @Action = 'DELETE';
    
    -- 获取受影响的第一条记录（生产环境需循环或使用游标）
    SELECT TOP 1 
        @RecordID = CAST(COALESCE(i.ProductID, d.ProductID) AS VARCHAR),
        @NewData = (SELECT * FROM inserted FOR JSON AUTO),
        @OldData = (SELECT * FROM deleted FOR JSON AUTO)
    FROM (SELECT 1 AS dummy) dummy
    LEFT JOIN inserted i ON 1=0
    LEFT JOIN deleted d ON 1=0;
    
    INSERT INTO AuditLog (TableName, Action, RecordID, OldData, NewData, ChangeUser, ChangeTime, HostName, AppName)
    VALUES ('Products', @Action, @RecordID, @OldData, @NewData, 
            SUSER_NAME(), GETDATE(), HOST_NAME(), APP_NAME());
END

4.2 复杂业务规则

-- 防止超额订购
CREATE TRIGGER trg_OrderDetails_CheckStock
ON OrderDetails
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Products p ON i.ProductID = p.ProductID
        WHERE i.Quantity > p.Stock
    )
    BEGIN
        RAISERROR('订购数量超过库存', 16, 1);
        ROLLBACK;
        RETURN;
    END
    
    -- 扣减库存（也可在 AFTER 中做）
    UPDATE p
    SET Stock = Stock - i.Quantity
    FROM Products p
    JOIN inserted i ON p.ProductID = i.ProductID;
END

4.3 数据同步（替代方案）

-- 触发器同步到历史表
CREATE TRIGGER trg_Orders_SyncToHistory
ON Orders
AFTER UPDATE
AS
BEGIN
    -- 将修改前的数据存入历史表
    INSERT INTO OrdersHistory (OrderID, CustomerID, Amount, Status, ModifiedDate)
    SELECT OrderID, CustomerID, Amount, Status, GETDATE()
    FROM deleted;
END

五、性能陷阱与优化

5.1 触发器性能问题

问题后果解决方案
大量数据更新触发器逐行处理基于集合操作，不要使用游标
嵌套触发器性能指数下降禁用不必要的嵌套
递归触发器无限循环谨慎使用，设置最大递归深度
触发器中的事务长事务锁表尽量减少触发器内逻辑
日志膨胀大量审计数据异步写入或采样记录
-- ❌ 坏：触发器中的游标
CREATE TRIGGER trg_Bad ON Orders AFTER UPDATE
AS
BEGIN
    DECLARE cur CURSOR FOR SELECT OrderID FROM inserted;
    OPEN cur;
    FETCH NEXT...
    -- 逐行处理 10000 行 → 极慢
END

-- ✅ 好：基于集合
CREATE TRIGGER trg_Good ON Orders AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (OrderID, OldStatus, NewStatus)
    SELECT i.OrderID, d.Status, i.Status
    FROM inserted i
    JOIN deleted d ON i.OrderID = d.OrderID
    WHERE i.Status <> d.Status;  -- 只处理实际变化的行
END

5.2 避免递归触发器

-- 场景：更新订单状态时自动更新客户等级，客户等级变化又触发订单更新 → 死循环

-- 解决方案1：禁用递归触发器
ALTER DATABASE YourDB SET RECURSIVE_TRIGGERS OFF;

-- 解决方案2：使用 TRIGGER_NESTLEVEL 检测
CREATE TRIGGER trg_UpdateCustomerLevel
ON Orders
AFTER UPDATE
AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1 RETURN;  -- 避免递归
    -- 正常逻辑
END

六、约束 vs 触发器：如何选择？

-- ✅ 使用约束的场景
-- 1. 非空、唯一、主键
-- 2. 简单取值范围（CHECK）
-- 3. 外键关系

-- ⚠️ 使用触发器的场景
-- 1. 审计日志
-- 2. 复杂跨表验证
-- 3. 级联更新（外键级联无法满足时）
-- 4. 视图上的 DML 操作
-- 5. DDL 操作审计

-- ❌ 不应使用触发器的场景
-- 1. 可用约束替代（如简单 CHECK）
-- 2. 大批量 ETL 操作（触发器会大幅降低速度）
-- 3. 逻辑可以放在应用层或存储过程

七、监控触发器

-- 查看所有触发器
SELECT 
    OBJECT_NAME(parent_id) AS TableName,
    name AS TriggerName,
    is_instead_of_trigger,
    is_disabled,
    OBJECT_DEFINITION(object_id) AS TriggerCode
FROM sys.triggers
WHERE parent_class_desc = 'OBJECT_OR_COLUMN';

-- 查看触发器执行统计
SELECT 
    OBJECT_NAME(st.objectid) AS TriggerName,
    st.execution_count,
    st.total_worker_time / st.execution_count AS avg_cpu_us,
    st.total_logical_reads / st.execution_count AS avg_logical_reads
FROM sys.dm_exec_trigger_stats st
WHERE st.database_id = DB_ID()
ORDER BY st.total_worker_time DESC;

-- 查看触发器的依赖关系
SELECT 
    referencing.name AS TriggerName,
    referenced.name AS ReferencedTable
FROM sys.sql_expression_dependencies d
JOIN sys.objects referencing ON d.referencing_id = referencing.object_id
JOIN sys.objects referenced ON d.referenced_id = referenced.object_id
WHERE referencing.type = 'TR';

八、核心总结

知识点核心要点
优先使用约束性能好、声明式、易维护
触发器慎用性能差、调试难、隐蔽性强
AFTER 触发器最常用，用于审计和附加逻辑
INSTEAD OF用于视图、特殊拦截逻辑
inserted/deleted虚拟表，需基于集合操作
递归/嵌套可能导致性能问题或死循环
审计日志触发器的经典场景

一句话记住本期内容：

约束是数据完整性的第一道防线，触发器是最后的手段——能用 CHECK 不用触发器，能用外键不用触发器，只在审计、复杂跨表验证时才考虑触发器。

动手练习

-- 问题1：如何阻止周末删除订单数据？

-- 问题2：设计一个触发器，当订单金额超过 10000 时，自动将订单状态设为 'VIP'，并发送通知到 Notification 表。

-- 问题3：下面的触发器有什么问题？
CREATE TRIGGER trg_UpdateStock
ON OrderDetails
AFTER INSERT
AS
BEGIN
    DECLARE @ProductID INT, @Quantity INT
    DECLARE cur CURSOR FOR SELECT ProductID, Quantity FROM inserted
    OPEN cur
    FETCH NEXT FROM cur INTO @ProductID, @Quantity
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Products SET Stock = Stock - @Quantity WHERE ProductID = @ProductID
        FETCH NEXT FROM cur INTO @ProductID, @Quantity
    END
    CLOSE cur
    DEALLOCATE cur
END
点击查看参考答案

问题1：

CREATE TRIGGER trg_PreventWeekendDelete
ON Orders
INSTEAD OF DELETE
AS
BEGIN
    IF DATEPART(WEEKDAY, GETDATE()) IN (1, 7)  -- 周日=1, 周六=7（取决于 SET DATEFIRST）
    BEGIN
        RAISERROR('周末禁止删除订单', 16, 1);
        RETURN;
    END
    DELETE FROM Orders WHERE OrderID IN (SELECT OrderID FROM deleted);
END

问题2：

CREATE TRIGGER trg_Orders_VIPCheck
ON Orders
AFTER INSERT, UPDATE
AS
BEGIN
    INSERT INTO Notifications (OrderID, Message, CreateTime)
    SELECT OrderID, '订单金额超10000，已自动升级为VIP订单', GETDATE()
    FROM inserted
    WHERE Amount > 10000;
    
    UPDATE Orders SET Status = 'VIP' WHERE OrderID IN (SELECT OrderID FROM inserted WHERE Amount > 10000);
END

问题3：问题：使用游标逐行处理，性能极差。优化：

CREATE TRIGGER trg_UpdateStock_Optimized
ON OrderDetails
AFTER INSERT
AS
BEGIN
    UPDATE p
    SET Stock = Stock - i.Quantity
    FROM Products p
    JOIN (SELECT ProductID, SUM(Quantity) AS Quantity FROM inserted GROUP BY ProductID) i
        ON p.ProductID = i.ProductID;
END

下一期预告

事务与并发控制（开发视角）—— 避免死锁与数据异常

  • 乐观锁 vs 悲观锁的实现
  • 应用层处理死锁的重试逻辑
  • 隔离级别的开发选择指南
  • 使用行版本控制避免读写阻塞

📌 本文代码已在 SQL Server 2019+ 验证。触发器虽强大，但会带来维护和调试的复杂性，建议优先考虑约束和存储过程。

本系列持续更新中，点击关注不错过第八期（开发系列收官之作）。

avatar
文章
阅读
粉丝