OK, if I can't use a distributed transaction WITHIN an autonomous transaction, and the PRAGMA is there to handle not the distributed SELECT query, but a different SELECT, it sounds like I could just push the autonomous transaction down into a separate stored proc, to minimize the scope of the AUTONOMOUS pragma? Sound right?

Re the need for and results of the autonomous transaction: I have two tables, call them Parent and Child, with one Parent row for zero to many Children. The parent row has a "group" column indicating what "group" the parent is in. Given a userid and a group, I have a function that returns True or False on group membership.

The business rule for updating either table is that you may only modify a child or parent if you belong to the same "group" as the child or parent. So when the proposed update is on a child, it is necessary to lookup the parent row in order to determine group membership. Since I am doing the check within a trigger, I use an autonomous transaction for the lookup because it's possible that the parent and child are both being modified within a single transaction (making the parent mutating). The autonomous nature of the transaction means that I see the parent as it existed before the current transaction started. If the parent did not even exist before the current transaction started, then the autonomous transaction finds no parent which can be handled fine.

Clearly the update logic would be simpler to code and test, and would perform better, if I used a stored proc and simply forbade direct UPDATE's to both tables. However from the point of view of higher-level code, the stored procs are an inferior interface (taken to an extreme, if we use stored procs for all data updates, why even have a SQL-compliant database). I think I understand the pros and cons of using stored procs versus trigger code, and I have chosen stored procs in cases where their benefits outweighed their negatives. The case in question here is just not one of those times, and I would like to make the trigger option work.

How about cascading logic? Either a parent trigger wants to cascade primary key changes to children for example, or a trigger-maintained value in the parent table, e.g. ChildCount, needs to be incremented or decremented during insert or delete of a child. The appropriate table then issues its own updates.

Though I must admit that when I look closely at the actual situation I face, I think I CAN do with the autonomous transaction as you said.

Yes, thank you.

Hi Tom,
Description of oerr 6519 is as follows:
--------------------------------------------------------
Cause: Before returning from an autonomous PL/SQL block, all autonomous transactions started within the block must be completed (either committed or rolled back). If not, the active autonomous transaction is implicitly rolled back and this error is raised.

--------------------------------------------------------

It means that we need to have either commit or rollback in the trigger if we use autonomous_transaction pragma.

Is there any usage of having a trigger with AUTONOMOUS_TRANSACTION, without commit or rollback? If yes, can you please explain.
If not,Is it not possible for oracle to catch this error during compilation stage itself instead of allowing user to create the trigger and raise the exception during usage?

Thanks in advance.
Giridhar

Thanks Tom for the information and for quick reply.