SQL触发器回滚事务时如何避免返回错误导致旧应用崩溃

核心逻辑 #

原有触发器使用FOR UPDATE后触发逻辑，必须通过回滚事务撤销已执行的修改，回滚操作会直接抛出数据库错误到上层应用。替换为**INSTEAD OF UPDATE替代触发器**即可解决问题：该类触发器会在更新操作实际执行前触发，直接替代原有更新逻辑，无需回滚事务，不会抛出任何错误，应用会认为操作执行成功。

修正后触发器代码 #

ALTER TRIGGER [dbo].[trgUpdateTasks] 
   ON  [dbo].[Tasks] 
   INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    -- 仅更新关联Jobs无QuotationID的记录
    UPDATE t
    SET 
        -- 此处需把Tasks表所有可更新字段按顺序列全，赋值为INSERTED对应的字段值，示例如下：
        t.JobID = i.JobID,
        t.TaskName = i.TaskName,
        t.TaskContent = i.TaskContent,
        t.CreateTime = i.CreateTime
        -- 其他字段依次补充
    FROM Tasks t
    INNER JOIN INSERTED i ON t.TaskID = i.TaskID -- TaskID为Tasks表主键，按实际字段名修改
    LEFT JOIN Jobs j ON i.JobID = j.JobID
    WHERE j.QuotationID IS NULL
END

注意：SET部分需要把你实际Tasks表中所有允许更新的字段都列全，赋值为INSERTED表对应的字段值，否则更新会丢失字段。

效果说明 #

• 关联的Jobs存在QuotationID的Tasks记录会被直接跳过更新，不会产生任何修改
• 整个过程无事务回滚，不会抛出任何数据库错误，老旧应用可以正常接收执行成功的响应
• 支持批量更新多行Tasks记录的场景，修复了原触发器仅能处理单条更新的缺陷

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