COMMIT Inside Trigger

How PRAGMA AUTONOMOUS_TRANSACTION is used in Trigger?Can we have COMMIT inside Trigger?

Yes, you can commit inside the trigger.

·       But for this you have to make this trigger transaction to be an Independent transaction from its parent transaction, you can do this by using Pragma.

·       Pragma AUTONOMOUS_TRANSACTION allow you to build the Independent (child) Transaction, started by another.

·       Should be declare in DECLARE section of any subprogram.

Let’s see how this works-

First Create below two tables.

SQL> CREATE TABLE MyTable(C1 NUMBER);

Table created.

SQL> CREATE TABLE MyTableLog(Timestamp DATE,Operation Varchar2(30));

Table created.

Now create After Insert Trigger on MyTable which will capture logs into MyTableLog.

SQL> CREATE TRIGGER MyTabTrigger
  2  AFTER INSERT On MyTable
  3  BEGIN
  4  INSERT INTO MYTableLog Values(SYSDATE,'Insert Operation Performed');
  5  COMMIT;
  6  END;
  7  /

Trigger created.

Now Insert Values into MyTable and see if above trigger works.

SQL> INSERT INTO MyTable Values(123);
INSERT INTO MyTable Values(123)
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "HR.MYTABTRIGGER", line 3
ORA-04088: error during execution of trigger 'HR.MYTABTRIGGER'

ERROR- Cannot COMMIT Inside Trigger.

Autonomous transactions:
As workaround, one can use Autonomous Transactions. Autonomous Transactions execute separate from the current transaction. Unlike Regular Triggers, Autonomous Triggers can contain COMMIT and ROLLBACK statements.

Now make changes to above Trigger to make it Independent Transaction using PRAGMA AUTONOMOUS TRANSACTION.

SQL> CREATE OR REPLACE TRIGGER MyTabTrigger
  2  AFTER INSERT On MyTable
  3  DECLARE
  4  PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6  INSERT INTO MyTableLog Values(SYSDATE,'Insert Operation Performed');
  7  COMMIT; --Only Allowed in AUTONOMOUS TRANSACTION
  8  END;
  9  /

Trigger created.

Now Insert Values into MyTable and see if it works.

SQL> Alter SESSION Set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> INSERT INTO MyTable Values(111);

1 row created.

SQL> /*Check the details on MyTableLog*/
SQL> SELECT * From MyTableLog;

TIMESTAMP          OPERATION
------------------ ------------------------------
02-NOV-16 23:00:20 Insert Operation Performed

SQL> INSERT INTO MyTable Values(55);

1 row created.

SQL> SELECT * From MyTableLog;

TIMESTAMP          OPERATION
------------------ ------------------------------
02-NOV-16 23:00:20 Insert Operation Performed
02-NOV-16 23:00:40 Insert Operation Performed

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

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.