Adsense Ad

Tuesday 14 March 2017

Use of Commit in Oracle PL/SQL Trigger



Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott

SQL> CREATE TABLE COMMIT_ON_TRIGGER(ID NUMBER, NAME VARCHAR2(10));

Table created

SQL> CREATE TABLE CHK_COMMIT_ON_TRIGGER(ID NUMBER);

Table created

SQL> 
SQL> CREATE OR REPLACE TRIGGER CHK_COMMIT_TRIGGER
  2  AFTER INSERT ON COMMIT_ON_TRIGGER FOR EACH ROW
  3  BEGIN
  4  INSERT INTO CHK_COMMIT_ON_TRIGGER VALUES(:NEW.ID);
  5  COMMIT;
  6  END;
  7  /

Trigger created

SQL> INSERT INTO COMMIT_ON_TRIGGER VALUES (1,'TEST');

INSERT INTO COMMIT_ON_TRIGGER VALUES (1,'TEST')

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.CHK_COMMIT_TRIGGER", line 3
ORA-04088: error during execution of trigger 'SCOTT.CHK_COMMIT_TRIGGER'


SQL> CREATE OR REPLACE TRIGGER CHK_COMMIT_TRIGGER
  2  AFTER INSERT ON COMMIT_ON_TRIGGER FOR EACH ROW
  3  DECLARE
  4  PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6  INSERT INTO CHK_COMMIT_ON_TRIGGER VALUES(:NEW.ID);
  7  COMMIT;
  8  END;
  9  /

Trigger created

SQL> INSERT INTO COMMIT_ON_TRIGGER VALUES (1,'TEST');

1 row inserted

SQL> SELECT * FROM COMMIT_ON_TRIGGER;

        ID NAME
---------- ----------
         1 TEST

SQL> 
SQL> SELECT * FROM CHK_COMMIT_ON_TRIGGER;

        ID
----------
         1

SQL>


NOTE THAT ONLY IN AUTONOMOUS TRIGGER ARE ALLOWED FOR USES COMMIT/ROLLBACK AND AFTER PERFORMING TRANSACTION COMMIT REQUIRED FOR ON SESSION TRANSACTION. COMMIT IN TRIGGER ONLY SAVE TRANSACTION PERFORMED INSIDE TRIGGER.

No comments: