Commit in trigger : Trigger and Transaction « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Trigger and Transaction

SQL>
SQL> CREATE TABLE employee_compensation (
  2     company VARCHAR2(100),
  3     NAME VARCHAR2(100),
  4     compensation NUMBER);

Table created.

SQL>
SQL> CREATE TABLE employee_history (
  2     NAME VARCHAR2(100),
  3     description VARCHAR2(255),
  4     occurred_on DATE);

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
  2     BEFORE INSERT
  3     ON employee_compensation
  4     FOR EACH ROW
  5  DECLARE
  6     PRAGMA AUTONOMOUS_TRANSACTION;
  7  BEGIN
  8     INSERT INTO employee_history
  9          VALUES (:NEW.NAME, 'BEFORE INSERT', SYSDATE);
 10
 11     COMMIT;
 12  END;
 13  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
  2     AFTER INSERT
  3     ON employee_compensation
  4     FOR EACH ROW
  5  DECLARE
  6     PRAGMA AUTONOMOUS_TRANSACTION;
  7  BEGIN
  8     IF :NEW.compensation > 1000000000
  9     THEN
 10        RAISE VALUE_ERROR;
 11     ELSE
 12        INSERT INTO employee_history VALUES (:NEW.NAME, 'AFTER INSERT', SYSDATE);
 13        COMMIT;
 14     END IF;
 15  EXCEPTION
 16     WHEN OTHERS
 17     THEN
 18        ROLLBACK;
 19        RAISE;
 20  END;
 21  /

Trigger created.

SQL> COLUMN name FORMAT a20
SQL> COLUMN description FORMAT a30
SQL>
SQL> SELECT NAME, description
  2       , TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
  3    FROM employee_history;

no rows selected

SQL>
SQL> BEGIN
  2     INSERT INTO employee_compensation VALUES ('B', 'J', 9100000);
  3
  4     INSERT INTO employee_compensation VALUES ('B', 'A', 10700000);
  5
  6     INSERT INTO employee_compensation VALUES ('B', 'Sally Bigdeal', 1000000001);
  7
  8  END;
  9  /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "JAVA2S.AFT_INS_CEO_COMP", line 15
ORA-04088: error during execution of trigger 'JAVA2S.AFT_INS_CEO_COMP'
ORA-06512: at line 6


SQL>
SQL> SELECT   NAME, description
  2         , TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
  3      FROM employee_history
  4  ORDER BY occurred_on;

NAME                 DESCRIPTION                    OCCURRED_ON
-------------------- ------------------------------ -------------------
J                    BEFORE INSERT                  07/24/2008 08:03:16
J                    AFTER INSERT                   07/24/2008 08:03:16
Sally Bigdeal        BEFORE INSERT                  07/24/2008 08:03:16
A                    AFTER INSERT                   07/24/2008 08:03:16
A                    BEFORE INSERT                  07/24/2008 08:03:16

SQL>
SQL>
SQL> DROP TABLE employee_compensation;

Table dropped.

SQL>
SQL> DROP TABLE employee_history;

Table dropped.

SQL>