Introduction Autonomous transactions are useful for implementing actions that need to be performed independently, regardless of whether the calling transaction commits or rolls back, such as transaction logging and retry counters. Implementation Use the pragma AUTONOMOUS_TRANSACTION.
Example:.
{codecitation class="brush:sql; gutter: true;" width="500px"} SQL> CREATE TABLE test_autonomous (
colA VARCHAR2(100) );
Table created
SQL> CREATE OR REPLACE PROCEDURE Test_Auto IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO test_autonomous
VALUES('Autonomous Transaction');
COMMIT;
END Test_Auto;
/
Procedure created
SQL> SELECT *
FROM test_autonomous;
No records selected.
SQL> DECLARE
BEGIN
INSERT INTO test_autonomous
VALUES('Main Transaction');
-- call the autonomous transaction
Test_Auto();
-- rollback the main transaction
ROLLBACK;
END;
/
PL/SQL procedure successfully completed
SQL> SELECT *
FROM test_autonomous;
{/codecitation}
COLA
------------------------------------------------
Autonomous Transaction According to the above example the autonomous transaction is not affected by the main transaction’s ROLLBACK. When to use Autonomous Transactions? you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries. - Commits and rollbacks in your database triggers
If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code. Suppose that you want to let a user try to get access to a resource N times before an outright rejection; you also want to keep track of attempts between connections to the database. This persistence requires a COMMIT, but one that should remain independent of the transaction. You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application. Thank you AAruran
|