Oracle autonomous transactions

Trigger implementation is required for processing a business requirement in the project yesterday. The trigger involves triggering table operations after inserting, deleting, and updating. Operations in inserting and updating can be implemented normally, that is, when deleting is used, it is required to update other tables. The updated value is the query operation on the trigger table itself. I have tried the N method to meet the requirement within a limited horizontal range, finally, it was fruitless. However, I had to ask the manager for help and finally solve the problem. It was indeed an oracle autonomous transaction that I was not familiar, in addition, this autonomous transaction is an independent transaction operation. Considering transaction rollback, it must be written as a trigger to complete the task. The problem is finally solved by the boss. Every time the problem is expected to be a growth opportunity, there will be gains, so I went to the Internet to find instructions and usage on autonomous affairs (AT for short). In the afternoon, I came to the garden to mark it for my own learning and reference by all garden friends;

1. Usage of autonomous transactions

Audit that cannot be rolled back: Generally, when a trigger is used to prohibit operations such as table updates, if logs are recorded, the trigger will cause log rollback when an exception is thrown. Autonomous transactions can prevent this point.
Avoid variant table: the table that triggers the trigger in the trigger.
Use ddl to write data to a database in a trigger: write operations (insert, update, delete, create, alter, and commit) are performed on the database) stored Procedures or functions cannot be simply called using SQL. In this case, you can set them as autonomous transactions, this avoids errors such as ora-14552 (ddl, commit, rollback cannot be performed in a query or dml), ora-14551 (dml operations cannot be performed in a query), and so on. Note that the function must have a return value, but only the in parameter (the out or in/out parameter is not allowed ).
Develop more modular code: in large-scale development, autonomous transactions can make the code more modular. failure or success does not affect other operations of the caller. The cost is that the caller has lost control of this module, in addition, the module cannot reference data not submitted by the caller.

2. Oracle self-made transactions refer to stored procedures and functions that can handle internal transactions independently from external transactions. They are declared using pragma autonomous_transaction to create an autonomous transaction, you must use the PRAGMA AUTONOMOUS_TRANSACTION statement in PL/SQL at the top of an anonymous block or in the definition of stored procedures, functions, data packets, or triggers. The SQL statements executed in such a module or process are autonomous.

View Code

create or replace procedure AutoNomouse_Insert is PRAGMA AUTONOMOUS_TRANSACTION; 

begin 

insert into Msg values('AutoNomouse Insert');   

commit;  

 end;

3. Autonomous transactions can be nested. The nested depth and so on are restricted only by the init. ora parameter transactions (number of concurrent transactions, 1.1 times of sessions by default. Because the autonomous transaction is separated from the main transaction (MT), it cannot detect the current status of the modified row. It seems that they are always in separate sessions before the primary transaction is committed, and they are unavailable for autonomous transactions. However, in turn, the situation is different: the main transaction can detect the results of self-governing transactions that have been executed.

4. If AT tries to access the resources controlled by MT, deadlock may occur. The Package cannot be declared as AT. Only the function and procedure owned by the package can be declared as. The primary transaction and autonomous transaction are completely different transactions, so they cannot share locks. To end an autonomous transaction, you must commit a commit, rollback, or execute ddl; otherwise, an Oracle error ORA-06519 is generated: active autonomous transaction detected and rolled back. The storage point cannot be rolled back to a storage point in the parent transaction in the autonomous transaction. It can only be used internally.

5. Possible Errors
Ora-06519-check for active autonomous transactions, rollback-No commit, rollback, or ddl operations to exit autonomous transactions
Ora-14450-attempting to access a transaction-level temporary table in use
Ora-00060-deadlock detected while waiting for resources