英文:
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:
- Because the
ENABLE TRIGGER
statement is never hit,myTrigger
remains disabled. - The
ROLLBACK
statement in theCATCH
block rolls back everything that was attempted in the transaction, including theDISABLE TRIGGER
statement, meaningmyTrigger
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论