0

I have a trigger that calls a stored proc (after insert on a table) to do a number of DML statements. My stored procedure has a commit so my trigger fails. After a bit of researching, I added pragma autonomous_transaction to the trigger and now it doesn't complain. But I'm not sure how this will affect the behaviour of my trigger. Is this the right way to do it or just a "hack" to get it to work?

This is the pseudo code of what I'm trying to do

proc1 (input) - program logic then insert into table X, commit.

proc2 (input) - program logic then insert into table Y, commit.


trigger on table A AFTER INSERT
declare pragma autonomous_transaction

if :new value = 1 then
  proc1 (:new value)
else
  proc2 (:new value)
end if
Tommy
  • 1
  • 1
  • Please post the whole code. – Ankit Bajpai Sep 04 '21 at 09:26
  • You should probably either not explicitly `commit` within the context of a trigger, to allow the containing transaction to control behavior, or go ahead and use `autonomous` behavior. The following may help decide which approach you might want: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/static-sql.html#GUID-BC564905-51F4-4ADB-B300-9F15B1AC3099 ... Keep in mind autonomous behavior could cause a deadlock between it and the containing transaction. – Jon Armstrong Sep 04 '21 at 09:32
  • You need to check the mechanism of autonomous transaction [in the docs](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/static-sql.html#GUID-BC564905-51F4-4ADB-B300-9F15B1AC3099) and decide. – astentx Sep 04 '21 at 10:19

1 Answers1

2

Let us consider the program flow:

  • You start a transaction.
  • You perform a DML operation on your table.
  • Your trigger fires and is an autonomous transaction so creates a separate transaction.
  • Your original transaction continues.
  • Maybe you perform additional DML actions in that original trasnsaction.
  • One of those additional DML actions raises an exception.
  • Your entire transaction (but not the separate autonomous transaction) is rolled back.

However:

  • You started a separate autonomous transaction.
  • That calls the stored procedure.
  • Which does a number of DML statements.
  • Then the trigger commits them.

You are left in a state where you have two transactions, the original one has been rolled back and the autonomous transaction has been committed.

If the autonomous transaction is used for logging and you want it to always document the actions whether the original transaction failed or was successful then this behaviour is fine.

If the autonomonous transaction is part of the normal business logic then you are potentially left in an inconsistent state where half the transaction was rolled back and half was committed; this is not what you want.

In the latter case, the normal solution would to be to remove the COMMIT from the trigger and make it a non-autonomous transaction and then the DML resulting from the trigger will be part of the original transaction and will be committed (or rolled back) with that transaction.

This is the pseudo code of what I'm trying to do

proc1 (input) - program logic then insert into table X, commit.
proc2 (input) - program logic then insert into table Y, commit.

See: What is the effect of placing the commit after DML in procedure?

In general, you should NOT put a COMMIT in a procedure so your pseudo-code should be:

proc1 (input) - program logic then insert into table X.
proc2 (input) - program logic then insert into table Y.

and your trigger should not be an autonomous transaction.

Then your program flow is:

  1. Insert into table A
  2. Trigger fires
  3. Depending on value, call proc1 or proc2.
  4. Complete the procedure.
  5. Complete the trigger.
  6. Complete the transaction and COMMIT (either from the code that performed the insert or automatically as the transaction closes).
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Say if the trigger is on Table A AFTER INSERT. And the trigger is set to autonomous transaction. The code within the trigger has no DML, just a whole bunch of If statements, the only time it does a DML is when it calls a stored proc which inserts into Table B a record. If I am understanding this correctly, you are saying if the insert into TABLE A rolls back, the record in table B is still there? But the trigger is AFTER insert of TABLE A, if the insert errrored out, the trigger wouldn't have fired in the first place would it? – Tommy Sep 04 '21 at 11:18
  • @Tommy The point I am making is that if the trigger and the DML it invokes is part of the same set of operations as the original DML then it should be part of the same transaction and do not use an autonomous transaction. If the trigger and the DML it invokes is independent from the original DML (i.e. if you are logging an action regardless of whether that action succeeds or fails) then it should be an autonomous transaction. Work out if you need one or two transactions and go from that to a solution rather than trying to hack a `COMMIT` into the statement when it may not need to be there. – MT0 Sep 04 '21 at 19:24
  • Thank you, the problem is the proc is something I can't change, its not owned by me and I can't edit it. I can't edit out the commit. I may have to write my own version of it which is bad because if that one changed, mine would also need to change. – Tommy Sep 05 '21 at 23:32