如何创建其效果无法回滚的存储过程? [英] How do I create a stored procedure whose effects cannot be rolled back?
问题描述
我想要一个存储过程，将记录插入 tableA 并更新 tableB 中的记录.
I want to have a stored procedure that inserts a record into tableA and updates record(s) in tableB.
将从触发器中调用存储过程.
The stored procedure will be called from within a trigger.
即使回滚触发器的最外层事务，我也希望 tableA 中插入的记录存在.
I want the inserted records in tableA to exist even if the outermost transaction of the trigger is rolled back.
tableA 中的记录是线性连接的，我必须能够重建线性连接.
The records in tableA are linearly linked and I must be able to rebuild the linear connection.
对 tableA 的写访问只能通过触发器.
Write access to tableA is only ever through the triggers.
我该怎么做?
推荐答案
您正在寻找的是自治事务，而这些在今天的 SQL Server 中不存在.请对以下项目进行投票/评论:
What you're looking for are autonomous transactions, and these do not exist in SQL Server today. Please vote / comment on the following items:
您可以考虑使用 xp_cmdshell 或 CLR 离开 SQL 引擎重新进入(这些操作无法由 SQL Server 回滚)……但这些方法并非没有问题.
What you can consider doing is using xp_cmdshell or CLR to go outside the SQL engine to come back in (these actions can't be rolled back by SQL Server)... but these methods aren't without their own issues.
另一个想法是使用 INSTEAD OF 触发器 - 您可以登录/update 其他表，然后决定不继续进行实际操作.
Another idea is to use INSTEAD OF triggers - you can log/update other tables and then just decide not to proceed with the actual action.
编辑
按照@VoodooChild 的建议，您可以使用 @table 变量来临时保存您可以在回滚后引用的数据 - 与插入到#temp table.
And along the lines of @VoodooChild's suggestion, you can use a @table variable to temporarily hold data that you can reference after the rollback - this data will survive a rollback, unlike an insert into a #temp table.
这篇关于如何创建其效果无法回滚的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
