如何实现基于EPRCheck配置的MainDataTable条件Insert/Update触发器？

需要在MainDataTable表上创建触发器，根据EPRCheck表的配置向EPR表条件插入数据，具体规则如下：

• 当MainDataTable执行Insert操作，且EPRCheck中Insert功能启用时，向EPR插入数据
• 当MainDataTable执行Update操作，且EPRCheck中Update功能启用时，向EPR插入数据

请问如何调整触发器的WHERE子句实现该需求？是否需要将Insert和Update拆分为两个独立触发器？

EPRCheck表创建脚本 #

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EPRCheck')
BEGIN
DROP TABLE EPRCheck
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE EPRCheck
([TriggerTypeID] [int] NOT NULL, 
[TriggerTypeDesc] [varchar](6) NOT NULL, 
[IsEnabled] [bit] NOT NULL,
CONSTRAINT [PK_EPRCheck_TriggerTypeID] PRIMARY KEY CLUSTERED 
(
    [TriggerTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM EPRCheck WHERE TriggerTypeID = 1)
BEGIN
INSERT INTO EPRCheck
VALUES (1, 'Insert', 1)
END
GO

IF NOT EXISTS (SELECT * FROM EPRCheck WHERE TriggerTypeID = 2)
BEGIN
INSERT INTO EPRCheck
VALUES (2, 'Update', 1)
END
GO

IF NOT EXISTS (SELECT * FROM [dbo].[EPRCheck] WHERE TriggerTypeID = 3)
BEGIN
INSERT INTO EPRCheck
VALUES (3, 'Delete', 1)
END
GO

是否需要拆分为两个独立触发器？ #

不需要拆分。单个AFTER INSERT, UPDATE触发器完全可以满足需求，且维护成本更低。拆分两个触发器虽能实现功能，但会造成代码冗余，后续调整配置逻辑时需要同时修改两个触发器，反而增加维护复杂度。

WHERE子句调整方案 #

当前触发器的WHERE子句逻辑存在混淆，需针对Insert和Update场景做精准判断：

• Insert场景：此时deleted表为空，只需判断@insertIsEnabled = 1即可，无需对比数据差异（Insert本身就是新增数据）
• Update场景：此时inserted和deleted表均有数据，需同时满足两个条件：@updateIsEnabled = 1，且数据字段确实发生了变化（通过EXCEPT对比指定字段）

修改后的完整触发器脚本 #

IF EXISTS (SELECT 1 FROM sys.triggers WHERE [name] = 'TriggerEPRInsertUpdate')
BEGIN
DROP TRIGGER TriggerEPRInsertUpdate
END
GO

--Trigger Type
--INSERT = 1
--UPDATE = 2
--DELETE = 3

CREATE TRIGGER TriggerEPRInsertUpdate ON MainDataTable
AFTER INSERT, UPDATE
NOT FOR REPLICATION
AS
-- 一次性获取Insert和Update的启用状态，减少表查询次数
DECLARE @insertIsEnabled bit, @updateIsEnabled bit
SELECT 
    @insertIsEnabled = CASE WHEN TriggerTypeID = 1 THEN IsEnabled ELSE @insertIsEnabled END,
    @updateIsEnabled = CASE WHEN TriggerTypeID = 2 THEN IsEnabled ELSE @updateIsEnabled END
FROM EPRCheck
WHERE TriggerTypeID IN (1,2)

-- 只有当任一功能启用时才继续执行
IF @insertIsEnabled = 1 OR @updateIsEnabled = 1
BEGIN
    DECLARE @triggerTypeID int

    -- 判断当前触发类型
    IF EXISTS (SELECT 0 FROM inserted)
    BEGIN
        IF EXISTS (SELECT 0 FROM deleted)
        BEGIN 
            SET @triggerTypeID = 2 -- Update操作
        END 
        ELSE
        BEGIN
            SET @triggerTypeID = 1 -- Insert操作
        END
    END 

    -- 仅在符合触发类型且对应功能启用时执行插入
    IF (@triggerTypeID = 1 AND @insertIsEnabled = 1) OR (@triggerTypeID = 2 AND @updateIsEnabled = 1)
    BEGIN
        SET XACT_ABORT ON;
        SET NOCOUNT ON;
        BEGIN TRANSACTION

        INSERT INTO EPR
        SELECT 
            NEWID() AS DocumentID,
            GETDATE() AS CreateDateTime,
            NULL AS ProcessDateTime,
            @triggerTypeID AS TriggerTypeID,
            1 AS MessageStatus
        FROM inserted i
        LEFT JOIN deleted d ON i.ID = d.ID
        WHERE 
            -- Insert场景：功能启用且无deleted数据（即Insert操作）
            (@insertIsEnabled = 1 AND d.ID IS NULL)
            OR
            -- Update场景：功能启用且数据字段确实发生变化
            (@updateIsEnabled = 1 AND d.ID IS NOT NULL 
             AND EXISTS (SELECT i.Status, i.KeyDate, i.OtherDate, i.KeyCode, i.OtherCode 
                         EXCEPT
                         SELECT d.Status, d.KeyDate, d.OtherDate, d.KeyCode, d.OtherCode))

        COMMIT TRANSACTION
    END
END
GO

内容的提问来源于stack exchange，提问作者stonypaul