SQL 语句到 ROLLBACK 但审计插入到 COMMIT [英] SQL statements to ROLLBACK but Audit inserts to COMMIT

问题描述

SQL Server 2012

SQL Server 2012

我有一个主存储过程，它调用其他几个子"存储过程.主进程将子进程封装在一个事务中.

I have a Master stored proc, which calls several other 'child' stored procs. The master proc encapsulates the child procs in a transaction.

在主进程和子进程中，有许多插入到审计表中.

Throughout the master and child procs, there are many inserts into an audit table.

如果过程成功，我会得到一个详细的事件链.如果进程失败并回滚，我想在审计表中记录最后一个处理的子进程，但是整个事情都被回滚了，包括审计数据.有没有办法在回滚子 proc 更改的同时有选择地提交审计插入?

If the process succeeds, I get a detaied chain of events. If the process fails and rolls back, I would like to have a record of the last child proc processed in the Audit table, but the whole thing is rolled back, including the audit data. Is there a way to selectively commit the audit inserts while rolling back the child proc changes?

谢谢

推荐答案

您可以尝试在主过程中使用表变量，并将每个子过程的返回码插入到表变量中.表变量不受事务影响，而临时表受事务影响.

You can try using a table variable in the master procedure and inserting the return code of each child procedure into the table variable. Table variables are not affected by transactions, whereas temp tables are.

在主过程结束时，您可以从表变量中进行选择以返回子过程的结果.

At the end of your master procedure, you can select from the table variable to return the results of your child procedures.

这篇关于SQL 语句到 ROLLBACK 但审计插入到 COMMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！