PRAGMA AUTONOMOUS_TRANSACTION: homemade transaction

PRAGMA AUTONOMOUS_TRANSACTION: a self-made transaction database transaction is a unit operation. Either all operations are successful or all operations fail. In Oracle, a transaction starts from executing the first data management language (DML) statement until it executes a COMMIT statement, commits and saves the transaction, or executes a ROLLBACK statement, stop this operation. It is difficult to record the error information to the database table because the transaction fails to be re-run, the INSERT statement used to write log entries has not been completed yet. To address this dilemma, Oracle provides a convenient method, that is, autonomous transactions. An autonomous transaction starts from the current transaction and runs in its own context. They can be submitted or re-run independently without affecting running transactions. As a result, they form an ideal form of writing error log tables. When an error is detected in a transaction, you can insert a row in the error log table and submit it, and then roll back the primary transaction without losing this insert. Because the autonomous transaction is separated from the primary transaction, 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. To create an autonomous transaction, you must use the PRAGMA AUTONOMOUS_TRANSACTION statement in PL/SQL at the top of the anonymous block or in the stored procedure, function, data packet, or trigger definition section. The SQL Server statements executed in such a module or process are autonomous. The trigger cannot contain the COMMIT statement, unless the PRAGMA AUTONOMOUS_TRANSACTION flag exists. However, only the statements in the trigger can be committed, but not the primary transaction. Exp: Create table Msg (Msg varchar (50); homemade transactions: create or replace procedure AutoNomouse_Insert is PRAGMA AUTONOMOUS_TRANSACTION; begin insert into Msg values ('autonomouse insert'); commit; end; non-autonomous transactions: create or replace Procedure NonAutoNomouse_Insert as begin insert into Msg Values ('nonautonomouse insert'); commit; end;