英文:
Is it possible to skip an table from Rollback in Oracle?
问题
我有一个情景，需要验证数据并将有效数据插入一些表中，然后在Oracle包中更新审核表中的状态。当存在验证失败时，希望回滚除审核表信息以外的所有其他表数据。是否可以跳过一个表的回滚。在每个表添加状态到审核表之后。
感谢提前的帮助。
英文:
I am having a scenario where validating the data and inserting the valid data into some of the tables and updating the status into audit table in Oracle package. when there is a validation failure then want to revert all other table data except audit table information. Is it possible to skip rollback for one table. After each table adding status to audit table.
Thanks in advance for help.
答案1
得分: 2
通常，这是由一个单独的自主事务过程完成的，该过程修改审核表并提交所做的更改。
由于这是一个自主事务，因此commit不会影响主事务，所以一旦您执行rollback，您在所有其他表中所做的更改都将被回滚，但存储在审核表中的信息将保持不变。
英文:
Usually, it is done by a separate autonomous transaction procedure which modifies the audit table and commits changes being made.
As it is an autonomous transaction, that commit won't affect the main transaction so - once you rollback - changes you made in all other tables will be rolled back, but info stored in audit table will remain as is.
答案2
得分: 1
不可以。当您执行ROLLBACK操作时，整个事务都会被回滚，您无法选择哪些部分要回滚，哪些部分不回滚。
如果存在验证失败，并且想要撤销除审计表信息之外的所有表数据，您需要：
- 使用多个事务，在一个事务中执行（经过验证的）数据修改，在另一个事务中执行审计数据修改，然后回滚第一个事务并提交第二个事务。通常使用
AUTONOMOUS TRANSACTION来实现这一点。 - 使用单个事务，首先执行审计数据修改，创建一个
SAVEPOINT，然后执行（经过验证的）数据修改，如果它们失败，则使用ROLLBACK TO SAVEPOINT来撤销该保存点之后的所有更改。然后您可以选择COMMIT或在SAVEPOINT之前的上一次COMMIT之前回滚。 - 使用单个事务，首先执行审计数据修改并提交它们，然后执行（经过验证的）数据修改，如果它们失败，则回滚到最后一个
COMMIT之前，以撤销所有更改。
英文:
> Is it possible to skip rollback for one table.
No. When you ROLLBACK a transaction, the entire transaction is rolled back and you cannot pick and choose which parts of it are rolled-back or not.
> When there is a validation failure then want to revert all other table data except audit table information.
You need to either:
- Use multiple transactions and perform the (validated) data modifications in one transaction and the audit data modifications in a separate transaction and then
ROLLBACKthe first transaction andCOMMITthe second transaction. This is normally done using anAUTONOMOUS TRANSACTION. - Use a single transaction and perform the audit data modifications first, create a
SAVEPOINTand only then perform the (validated) data modifications and if they fail thenROLLBACK TO SAVEPOINTto undo all the changes after that save point. You can then choose toCOMMITor could stillROLLBACKto the previousCOMMITbefore theSAVEPOINT. - Use a single transaction and perform the audit data modifications first and
COMMITthem and only then perform the (validated) data modifications and if they fail thenROLLBACKto undo all the changes to that lastCOMMIT.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论