SQL ROLLBACK会撤销触发器的启用或禁用吗?

huangapple go评论68阅读模式
英文:

Does SQL ROLLBACK undo the enabling or disabling of triggers?

问题

SQL的ROLLBACK语句会回滚整个事务,包括DISABLE TRIGGER语句。因此,如果在"do something"部分发生错误,触发器myTrigger将保持启用。如果希望在错误发生时禁用触发器并在CATCH块中重新启用它,您需要在CATCH块中添加相应的逻辑来重新启用触发器。

英文:

Let's say I have a SQL script that looks something like this:

BEGIN TRAN
BEGIN TRY
    DISABLE TRIGGER dbo.myTrigger ON dbo.myTable;

    --do something that requires the trigger to be disabled, but which might throw an error

    ENABLE TRIGGER dbo.myTrigger ON dbo.myTable;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH

If an error gets thrown in the "do something" part, there are two possible things that could happen:

  1. Because the ENABLE TRIGGER statement is never hit, myTrigger remains disabled.
  2. The ROLLBACK statement in the CATCH block rolls back everything that was attempted in the transaction, including the DISABLE TRIGGER statement, meaning myTrigger remains enabled.

I would intuitively expect 2) to be true, but a colleague of mine suspects that 1) is true instead, and you would have to manually re-enable the trigger inside the CATCH block. Neither of us could find anything on Stack Overflow or elsewhere about which interpretation is correct. So here's the question: does SQL's ROLLBACK statement roll back the enabling or disabling of triggers?

答案1

得分: 0

经过我的测试,似乎 ROLLBACK 确实会撤销触发器的启用和禁用。如果我运行以下脚本:

BEGIN TRAN
BEGIN TRY
    DISABLE TRIGGER dbo.myTrigger ON dbo.myTable;

    RAISERROR('test', 11, 11);

    ENABLE TRIGGER dbo.myTrigger ON dbo.myTable;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH

然后运行以下查询:

SELECT is_disabled FROM sys.triggers WHERE name = 'myTrigger'

我会得到 "0",这意味着 DISABLE TRIGGER 语句被回滚了。

英文:

Having tested this myself, it seems that ROLLBACK does indeed undo the enabling and disabling of triggers. If I run the following script:

BEGIN TRAN
BEGIN TRY
    DISABLE TRIGGER dbo.myTrigger ON dbo.myTable;

    RAISERROR('test', 11, 11);

    ENABLE TRIGGER dbo.myTrigger ON dbo.myTable;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH

Then run the following query:

SELECT is_disabled FROM sys.triggers WHERE name = 'myTrigger'

I get back "0", meaning that the DISABLE TRIGGER statement was rolled back.

huangapple
  • 本文由 发表于 2023年6月15日 16:20:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76480479.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定