Using Commit/Rollback In Oracle Trigger.

1. CREATE TABLE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================

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

Table created.

2. CREATE TABLE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================

SQL> CREATE TABLE ERROR_CHECK(ID NUMBER);

Table created.

3. CREATE TRIGGER FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER==========================================================

SQL> CREATE OR REPLACE TRIGGER CHECT_COMMIT_TRIGGER
  2  AFTER INSERT ON TEST_TRIGGER
  3  BEGIN
  4  INSERT INTO ERROR_CHECK(ID) VALUES (10);
  5  COMMIT;
  6  END;
  7  /

Trigger created.

4. INSERTING VALUE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================

SQL> INSERT INTO TEST_TRIGGER(ID, NAME) VALUES(10,'RAJIB');
INSERT INTO TEST_TRIGGER(ID, NAME) VALUES(10,'RAJIB')
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "MICR.CHECT_COMMIT_TRIGGER", line 3
ORA-04088: error during execution of trigger 'MICR.CHECT_COMMIT_TRIGGER'

IN THIS PART YOU GET AN ERROR
==========================================================

5. DROP TRIGGER
==========================================================

SQL> DROP TRIGGER CHECT_COMMIT_TRIGGER;

Trigger dropped.

6. CREATE AUTONOMOUS TRIGGER FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER

NOTE THAT ONLY IN AUTONOMOUS TRIGGER ARE ALLOWED FOR USES COMMIT/ROLLBACK
====================================================================


SQL> CREATE OR REPLACE TRIGGER CHECT_COMMIT_TRIGGER
  2  AFTER INSERT ON TEST_TRIGGER
  3  DECLARE
  4  PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6  INSERT INTO ERROR_CHECK(ID) VALUES (10);
  7  COMMIT;
  8  END;
  9  /

Trigger created.


7. INSERTING VALUE FOR TEST COMMIT/ROLLBACK USE IN A TRIGGER
==========================================================
SQL> INSERT INTO TEST_TRIGGER(ID, NAME) VALUES(10,'RAJIB');

1 row created.

SQL>


SUCCESSFULLY INSERTED
===========================================================