CODEWITHSUNDEEP
oracle-databasestored-procedurestriggers
McArthey
McArthey

Reputation: 1646

Calling Oracle autonomous stored procedure from trigger

I have an Oracle trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure? I'm not positive of this since the ability to debug/log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION? Thanks

Upvotes: 1

Views: 4061

Answers (3)

Gary Myers
Gary Myers

Reputation: 35401

If you have a row level trigger on table_x, then that trigger can be fired multiple times by the same statement as different rows are impacted by that statement.

The order in which those rows are impacted is indeterminate. As such, the state of table_x is indeterminate during the execution of a row level trigger. This is why the MUTATING TABLE exception is raised.

An autonomous transaction 'cheats' by looking at the committed state of the table (ie excluding all changes made by that statement, and other statements in the transaction).

If you want a stored procedure to look at the state of table_x in response to activity on that table, then it needs to be done after all the rows changes have been made (ie in a statement level trigger, not a row level trigger).

The design pattern for this is often to set a flag (package level variable) in a row level trigger, check the flag in an AFTER statement level trigger, and if necessary action it and reset it.

Upvotes: 0

APC
APC

Reputation: 146329

"The trigger logic is conditionally updating Table B which calls the stored procedure to select from the values on Table A so that Table B can be updated with a calculated value. "

Perhaps Table B really ought to be a Materialized View derived from Table A? We can build a lot of complexity into the WHERE clauses of the queries which populate MViews. Find out more.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231781

Values passed in to a stored procedure as parameters will always be available to the stored procedure. It doesn't matter whether the procedure is declared using an autonomous transaction.

Code running in an autonomous transaction cannot see changes made by the calling transaction. 9 times out of 10, when people are describing problems seeing the data they expect, this is the source of the problem.

If your stored procedure is doing anything other than writing something to a log table, I would be exceptionally cautious about using autonomous transactions. If you are using autonomous transactions for anything other than logging, you are almost certainly using them incorrectly. And you are probably introducing a whole host of bugs related to race conditions and transactional integrity.

Upvotes: 5

Related Questions