SQL Server 2017动态触发器需求：满足条件时删除更新行

问题背景 #

需要为Microsoft SQL Server 2017 Enterprise中的1200余张表创建动态触发器，规则是：当更新行满足is_deleted=1且change_user='purge'时，直接删除该行而非保留更新后的数据。现有触发器会执行全表扫描，完全无法适配亿级数据、每小时200万事务的高并发场景，必须优化为仅处理本次更新触发的目标行。

原触发器的核心问题 #

原触发器执行DELETE FROM 表 WHERE is_deleted=1 AND change_user='purge'会扫描全表，在亿级数据量下会引发严重的性能瓶颈，阻塞其他事务，完全不符合高并发要求。

优化后的触发器方案 #

以下是单表的优化触发器代码，核心逻辑是通过主键关联Inserted表，仅定位到本次更新的行，避免全表扫描：

CREATE OR ALTER TRIGGER [PurgeDelete]
ON [TriggerTest]
AFTER UPDATE
AS
SET NOCOUNT ON; -- 高并发场景下减少不必要的网络开销

-- 无更新行时直接退出，避免无效执行
IF (ROWCOUNT_BIG() = 0)
    RETURN;

-- 仅当存在满足条件的更新行时执行删除逻辑
IF EXISTS (SELECT 1 FROM Inserted WHERE is_deleted = 1 AND LOWER(change_user) = 'purge')
BEGIN
    DECLARE @SchemaName NVARCHAR(128) = OBJECT_SCHEMA_NAME(@@PROCID);
    DECLARE @TableName NVARCHAR(128) = OBJECT_NAME(@@PROCID);
    DECLARE @PrimaryKeyCols NVARCHAR(MAX);
    DECLARE @DeleteSql NVARCHAR(MAX);

    -- 动态获取表的主键列，用于关联目标表与Inserted表
    SELECT @PrimaryKeyCols = STRING_AGG(QUOTENAME(c.COLUMN_NAME), ' AND ')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        ON tc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    WHERE tc.TABLE_SCHEMA = @SchemaName
      AND tc.TABLE_NAME = @TableName
      AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY';

    -- 拼接仅删除本次更新目标行的SQL语句
    SET @DeleteSql = N'DELETE t
                       FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + N' t
                       JOIN Inserted i ON ' + @PrimaryKeyCols + N'
                       WHERE i.is_deleted = 1 AND LOWER(i.change_user) = ''purge''';

    EXEC sp_executesql @DeleteSql;
END
GO

关键优化点

• 避免全表扫描：通过主键关联Inserted表，仅定位到本次更新的行，亿级数据下性能提升显著。
• SQL注入防护：使用QUOTENAME()处理表名、列名，避免特殊字符引发的注入风险。
• 兼容复合主键：利用STRING_AGG()（SQL Server 2017+支持）自动拼接多主键的关联条件。
• 大小写兼容：通过LOWER(change_user)处理大小写不一致的输入（如示例中的'Purge'）。
• 高并发友好：SET NOCOUNT ON减少网络传输，ROWCOUNT_BIG()快速退出无更新场景。

批量生成1200张表的触发器脚本 #

如果需要为所有包含is_deleted和change_user列的表自动创建触发器，可使用以下脚本：

DECLARE @TriggerTemplate NVARCHAR(MAX) = N'
CREATE OR ALTER TRIGGER [{SchemaName}].[PurgeDelete_{TableName}]
ON [{SchemaName}].[{TableName}]
AFTER UPDATE
AS
SET NOCOUNT ON;

IF (ROWCOUNT_BIG() = 0)
    RETURN;

IF EXISTS (SELECT 1 FROM Inserted WHERE is_deleted = 1 AND LOWER(change_user) = ''purge'')
BEGIN
    DECLARE @PrimaryKeyCols NVARCHAR(MAX);

    SELECT @PrimaryKeyCols = STRING_AGG(QUOTENAME(c.COLUMN_NAME), '' AND '')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        ON tc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    WHERE tc.TABLE_SCHEMA = ''{SchemaName}''
      AND tc.TABLE_NAME = ''{TableName}''
      AND tc.CONSTRAINT_TYPE = ''PRIMARY KEY'';

    DECLARE @DeleteSql NVARCHAR(MAX) = N''DELETE t
                                       FROM [{SchemaName}].[{TableName}] t
                                       JOIN Inserted i ON '' + @PrimaryKeyCols + N''
                                       WHERE i.is_deleted = 1 AND LOWER(i.change_user) = ''''purge'''''';

    EXEC sp_executesql @DeleteSql;
END
GO
';

DECLARE @SchemaName NVARCHAR(128), @TableName NVARCHAR(128);

-- 遍历所有同时包含is_deleted和change_user列的表
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT c.TABLE_SCHEMA, c.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.COLUMN_NAME IN ('is_deleted', 'change_user')
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME
HAVING COUNT(DISTINCT c.COLUMN_NAME) = 2;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 替换模板中的占位符生成触发器脚本
    DECLARE @TriggerSql NVARCHAR(MAX) = REPLACE(REPLACE(@TriggerTemplate, '{SchemaName}', @SchemaName), '{TableName}', @TableName);
    PRINT @TriggerSql; -- 先打印脚本确认无误
    -- EXEC sp_executesql @TriggerSql; -- 取消注释可直接创建触发器

    FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;
GO

使用说明

• 脚本会自动筛选出符合条件的表，生成命名为PurgeDelete_表名的触发器，避免重名。
• 建议先执行PRINT查看生成的脚本，确认无误后再取消注释EXEC语句批量创建。

测试示例 #

UPDATE [TriggerTest] 
SET [Description] = 'Test 1a', [is_deleted] = 1, [change_user] = 'Purge' 
WHERE [TriggerTestId] = 1

SELECT * FROM [TriggerTest]; -- 满足条件的行已被删除，无返回结果
GO

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