SQL Server数据迁移作业触发器问题及测试调试咨询

背景 #

我们通过定时SQL Server作业处理数据迁移业务，一月前接到新需求：需将远程数据库中的last_add2、batch_no_all、box_num2字段值迁移至gjbsh_sub表。更新向该表迁移数据的存储过程后，因该表存在INSERT和UPDATE触发器遇到问题，最终通过修改INSERT触发器实现需求，但后续同事反馈prod_det表频繁出现数据缺失。经分析，原触发器的IF NOT EXISTS语句仅能处理单行插入，无法支持批量插入，Claude给出了优化后的触发器代码，现针对两个问题解答如下：

1. Claude给出的优化方案的改进空间 #

Claude的方案解决了原触发器无法处理批量插入的核心问题，但仍存在以下可改进的点：

（1）修正主键匹配逻辑 #

prod_det表的主键是**(prod_no, prod_add, batch_no)**组合，而Claude的代码在UPDATE和INSERT的条件中仅匹配了prod_no和batch_no，忽略了prod_add字段。这会导致两种错误：

• 若prod_det中存在同一prod_no+batch_no但不同prod_add的记录，触发器会错误更新无关的旧记录，而非插入新的prod_add对应的记录；
• 若product表中last_add字段发生变更，新插入的gjbsh_sub记录无法在prod_det中生成对应新prod_add的记录。

修正方式：
在UPDATE和INSERT的WHERE条件中加入prod_add的匹配，关联product表的last_add字段：

-- UPDATE部分修正
UPDATE prod_det
   SET produce_date = CASE
                        WHEN i.produce_date IS NULL
                             OR i.produce_date = '2100-01-01'
                             OR i.produce_date = '1900-01-01'
                        THEN prod_det.produce_date          
                        ELSE i.produce_date
                      END,
       batch_no_all  = CASE
                        WHEN i.batch_no_all IS NULL OR LTRIM(RTRIM(i.batch_no_all)) = ''
                        THEN prod_det.batch_no_all           
                        ELSE i.batch_no_all
                      END,
       last_add2     = CASE
                        WHEN i.last_add2 IS NULL OR LTRIM(RTRIM(i.last_add2)) = ''
                        THEN prod_det.last_add2
                        ELSE i.last_add2
                      END,
       box_num2      = CASE
                        WHEN i.box_num2 IS NULL
                        THEN prod_det.box_num2
                        ELSE i.box_num2
                      END
  FROM inserted i
  JOIN product p ON p.prod_no = i.prod_no
 WHERE prod_det.prod_no  = i.prod_no
   AND prod_det.batch_no = i.batch_no
   AND prod_det.prod_add = p.last_add; -- 新增主键字段匹配

-- INSERT部分修正
INSERT prod_det (prod_no, prod_add, batch_no, avail_date, produce_date, batch_no_all, last_add2, box_num2)
SELECT i.prod_no, p.last_add, i.batch_no, i.avail_date, i.produce_date,
       i.batch_no_all, i.last_add2, i.box_num2
  FROM inserted i
  JOIN product p ON p.prod_no = i.prod_no
 WHERE NOT EXISTS (
          SELECT 1 FROM prod_det d
           WHERE d.prod_no  = i.prod_no
             AND d.batch_no = i.batch_no    
             AND d.prod_add = p.last_add -- 新增主键字段匹配
       );

（2）增加错误处理与日志记录 #

当前方案没有错误捕获机制，若触发器执行失败（如product表无对应prod_no、权限不足等），会导致原gjbsh_sub的插入操作失败，且无法定位问题。建议加入TRY/CATCH块，并记录错误日志：

ALTER TRIGGER [dbo].[gjbsh_sub_ins]
ON [dbo].[gjbsh_sub]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- 原UPDATE和INSERT逻辑...
    END TRY
    BEGIN CATCH
        -- 记录错误到自定义日志表（需提前创建log表）
        INSERT INTO dbo.TriggerErrorLog (ErrorTime, ErrorMessage, ErrorProcedure, AffectedTable)
        VALUES (GETDATE(), ERROR_MESSAGE(), ERROR_PROCEDURE(), 'gjbsh_sub_ins');
        
        -- 根据业务需求决定是否抛出错误，避免影响原插入操作
        -- THROW;
    END CATCH
END;

（3）优化并发控制 #

批量插入场景下，可能出现竞态条件（如多个会话同时插入同一prod_no+prod_add+batch_no的记录），建议在INSERT时使用UPDLOCK锁提示，防止数据丢失：

INSERT prod_det (prod_no, prod_add, batch_no, avail_date, produce_date, batch_no_all, last_add2, box_num2)
SELECT i.prod_no, p.last_add, i.batch_no, i.avail_date, i.produce_date,
       i.batch_no_all, i.last_add2, i.box_num2
  FROM inserted i
  JOIN product p ON p.prod_no = i.prod_no
 WHERE NOT EXISTS (
          SELECT 1 FROM prod_det d WITH (UPDLOCK)
           WHERE d.prod_no  = i.prod_no
             AND d.batch_no = i.batch_no    
             AND d.prod_add = p.last_add
       );

（4）避免不必要的更新 #

当前UPDATE逻辑会无条件执行，即使插入的字段值与prod_det现有值完全一致，会增加锁开销。可在UPDATE的WHERE条件中加入字段差异判断：

WHERE prod_det.prod_no  = i.prod_no
  AND prod_det.batch_no = i.batch_no
  AND prod_det.prod_add = p.last_add
  AND (
       prod_det.produce_date != CASE WHEN i.produce_date IS NULL OR i.produce_date IN ('2100-01-01','1900-01-01') THEN prod_det.produce_date ELSE i.produce_date END
       OR ISNULL(prod_det.batch_no_all,'') != ISNULL(i.batch_no_all,'')
       OR ISNULL(prod_det.last_add2,'') != ISNULL(i.last_add2,'')
       OR prod_det.box_num2 != i.box_num2
      );

（5）处理product表无匹配数据的场景 #

若inserted中的prod_no在product表中不存在，当前方案会直接跳过插入，导致prod_det数据缺失。可根据业务需求处理：

• 插入默认prod_add值；
• 抛出错误并终止原插入；
• 记录缺失的prod_no到日志表。

2. SQL Server触发器的安全测试与调试最佳实践 #

测试类实践 #

• 批量场景全覆盖测试：分别测试单行插入、10-100行批量插入、超大批量插入（如1000+行），验证触发器对所有数据的处理一致性，确保无部分数据丢失。
• 并发场景测试：使用多个会话同时插入数据，模拟生产环境的并发压力，检查是否出现死锁、数据不一致或重复插入的问题。
• 边界值与异常测试：
  • 测试默认值（如produce_date为2100-01-01、1900-01-01）；
  • 测试空值、超长字符串、数值边界值；
  • 注入无效数据（如product中不存在的prod_no），验证错误处理逻辑。
• 权限测试：使用低权限账户执行插入操作，验证触发器是否因权限不足导致失败，确保权限配置符合安全要求。

调试类实践 #

• 分步验证逻辑：在测试环境中先禁用触发器，手动执行触发器内的UPDATE/INSERT语句，验证每一步的执行结果，再启用触发器进行端到端测试。
• 增加临时日志输出：调试阶段可在触发器中加入PRINT语句或临时表记录关键变量、中间结果，方便定位问题。
• 使用SQL Server Profiler/Extended Events：捕获触发器执行的详细过程，包括执行时间、锁等待、错误信息等，分析性能瓶颈或异常原因。

运维类实践 #

• 触发器代码版本控制：将触发器代码纳入版本控制系统，记录每次修改的原因、内容和测试结果，方便追溯问题。
• 定期审计触发器执行：通过系统视图（如sys.triggers、sys.dm_exec_trigger_stats）监控触发器的执行频率、成功率和性能消耗。
• 事务一致性验证：测试当触发器执行失败时，原表的插入操作是否正确回滚，确保事务一致性符合业务要求。

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