Autonomous transaction

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

A trigger or procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION;. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions.

Example

SQL> CREATE OR REPLACE TRIGGER tab1_trig
  2    AFTER insert ON tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
  7    COMMIT; -- only allowed in autonomous triggers
  8  END;
  9  /
Trigger created.

SQL> INSERT INTO tab1 VALUES (1);
1 row created.

Note that with the above example will insert and commit log entries - even if the main transaction is rolled-back!

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#