Exception treatment with Commit and Rollback

[josehw]

Hello, I would like to know how to make a correct treatment of my triggers and procedures, using Commit and Rollback. it cost

[dr_gori]

Like this?
Do you want to do RollBack if you give any errors during the execution of your procedure?

[josehw]

Yes, for example, I have a trigger q has 2 inserts, what do I need is q when an error occurs in the 2nd insert, give a rollback in the information q have been inserted in the 1st insert, titted?

[marcelo_nunes]

If you are using Pragma AutoNomous_Transaction in the trigger, you will have to treat insert to insert for if this process falls on the exception it does a rollback, with this you put the commit only at the end of the trigger.
But if you are not using Pragma AutoNomous_Transaction, the trigger itself will not let the information be "commitates" and will give a rollback in everything that was performed there because you can not put commit in trigger.

[josehw]

Thanks for the attention and the answer!

[fsitja]

You do not need Autonomous Transaction in your case, since there is dependence between the two operations. Let the trigger give a raise on the exception on the 2nd insert normally. At the exit of your DML that fired the trigger, take the exception and give the rollback. Will cancel everything.

Now, very careful with Autonomous Transaction. The ideal is to treat everything in a transaction only and decide by the Commit or RollBack at the end, not to end up detonating with the consistency of the database.

[RodrigoValentim]

In trigger it is not these situations and yes in the application, why do not you test the situation before the process go to the bank?


Once the trigger action is made, there is no more rollback because of the Commit.