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.
Example 1:
CREATE OR REPLACE TRIGGER tab1_trig
AFTER INSERT
ON tab1
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO LOG
VALUES (SYSDATE, 'Insert on TAB1');
COMMIT; -- only allowed in autonomous triggers
END;
Example 2:
----------
CREATE TABLE xhl_test (
test_value VARCHAR2(25));
CREATE OR REPLACE PROCEDURE xhl_test1
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO xhl_test
(test_value
)
VALUES ('Child block insert'
);
COMMIT;
END xhl_test1;
CREATE OR REPLACE PROCEDURE xhl_test2
IS
BEGIN
INSERT INTO xhl_test
(test_value
)
VALUES ('Parent block insert'
);
xhl_test1;
ROLLBACK;
END xhl_test2;
exec xhl_test2;
select * from xhl_test
TRUNCATE TABLE xhl_test;
