如何在存储过程中实现原SQL触发器的功能？（解决性能问题）

核心思路 #

把触发器中捕获更新前后数据、插入日志的逻辑，显式整合到两个更新存储过程中，避免触发器隐式执行带来的性能开销，同时简化冗余的数据关联逻辑（原触发器中关联tbl_permissions表属于冗余操作，inserted/deleted已包含更新前后的完整数据）。

修改后的存储过程代码 #

1. sp_activation_email_update

ALTER PROCEDURE [dbo].[sp_activation_email_update]
    (@sEmail NVARCHAR(100),
     @lModifyUserId INT,
     @bEML  BIT)         
AS  
BEGIN
    SET NOCOUNT ON; -- 减少不必要的消息输出，提升性能

    -- 定义表变量，存储更新前的旧数据
    DECLARE @OldPermissions TABLE (
        pk_id INT,
        customer_id INT,
        type INT, -- 请根据原表`tbl_permissions`的`type`字段实际类型调整
        email BIT,
        gsm BIT
    );

    -- 提前捕获要更新的记录的旧值
    INSERT INTO @OldPermissions (pk_id, customer_id, type, email, gsm)
    SELECT p.pk_id, p.customer_id, p.type, p.email, p.gsm
    FROM dbo.tbl_permissions p
    INNER JOIN dbo.tbl_contact_info ci 
        ON p.customer_id = ci.customer_id
    WHERE ci.email = @sEmail;

    -- 执行更新操作
    UPDATE p
    SET email = @bEML,
        modify_user_id = @lModifyUserId,
        modify_date = GETDATE()
    FROM dbo.tbl_permissions p
    INNER JOIN @OldPermissions op 
        ON p.pk_id = op.pk_id;

    -- 仅当email值确实发生变化时，插入日志
    INSERT INTO dbo.tbl_permissions_log (
        customer_id, 
        type, 
        email_new_value, 
        email_old_value, 
        gsm_new_value, 
        gsm_old_value, 
        modify_user_id, 
        modify_date
    )
    SELECT 
        op.customer_id,
        op.type,
        @bEML,
        op.email,
        p.gsm, -- gsm未修改，新值与旧值一致
        op.gsm,
        @lModifyUserId,
        GETDATE()
    FROM @OldPermissions op
    INNER JOIN dbo.tbl_permissions p 
        ON op.pk_id = p.pk_id
    WHERE op.email <> @bEML; -- 过滤无变化的记录，减少日志写入量
END 

2. sp_activation_sms_update

ALTER PROCEDURE [dbo].[sp_activation_sms_update]
    (@sGsmNo NVARCHAR(15),
     @lModifyUserId INT,
     @bGsm BIT)      
AS  
BEGIN
    SET NOCOUNT ON;

    -- 定义表变量存储更新前的旧数据
    DECLARE @OldPermissions TABLE (
        pk_id INT,
        customer_id INT,
        type INT, -- 请根据原表`tbl_permissions`的`type`字段实际类型调整
        email BIT,
        gsm BIT
    );

    -- 提前捕获要更新的记录的旧值
    INSERT INTO @OldPermissions (pk_id, customer_id, type, email, gsm)
    SELECT p.pk_id, p.customer_id, p.type, p.email, p.gsm
    FROM dbo.tbl_permissions p
    INNER JOIN dbo.tbl_contact_info ci 
        ON p.customer_id = ci.customer_id
    WHERE ci.gsm_no = RIGHT(@sGsmNo, 10);

    -- 执行更新操作
    UPDATE p
    SET gsm = @bGsm,
        modify_user_id = @lModifyUserId,
        modify_date = GETDATE()
    FROM dbo.tbl_permissions p
    INNER JOIN @OldPermissions op 
        ON p.pk_id = op.pk_id;

    -- 仅当gsm值确实发生变化时，插入日志
    INSERT INTO dbo.tbl_permissions_log (
        customer_id, 
        type, 
        email_new_value, 
        email_old_value, 
        gsm_new_value, 
        gsm_old_value, 
        modify_user_id, 
        modify_date
    )
    SELECT 
        op.customer_id,
        op.type,
        p.email, -- email未修改，新值与旧值一致
        op.email,
        @bGsm,
        op.gsm,
        @lModifyUserId,
        GETDATE()
    FROM @OldPermissions op
    INNER JOIN dbo.tbl_permissions p 
        ON op.pk_id = op.pk_id
    WHERE op.gsm <> @bGsm; -- 过滤无变化的记录
END                 

关键优化点 #

1. 消除触发器隐式开销：移除触发器后，无需在每次更新时触发额外的隐式逻辑，减少数据库事务的额外开销。
2. 可控的旧数据捕获：用表变量预先存储要更新的记录旧值，比触发器的inserted/deleted临时表更灵活，避免更新后丢失原始数据。
3. 减少无效日志写入：仅当字段值真正改变时才插入日志，避免无意义的日志记录，降低IO开销。
4. 简化关联逻辑：去掉原触发器中冗余的tbl_permissions表关联，直接通过表变量获取旧数据，逻辑更简洁高效。

后续操作步骤 #

1. 先执行DISABLE TRIGGER [dbo].[trig_permissions] ON [dbo].[tbl_permissions];禁用触发器，测试修改后的存储过程功能是否正常。
2. 确认功能正常、性能达标后，执行DROP TRIGGER [dbo].[trig_permissions];彻底删除触发器。

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