Mark trigger with PRAGMA AUTONOMOUS_TRANSACTION : 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,
  5     layoffs NUMBER);

Table created.

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

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE employee_audit (
  2     name IN VARCHAR2,
  3     description IN VARCHAR2,
  4     occurred_on IN DATE
  5     )
  6  IS
  7     PRAGMA AUTONOMOUS_TRANSACTION;
  8  BEGIN
  9     INSERT INTO employee_history VALUES (
 10        employee_audit.name,
 11        employee_audit.description,
 12        employee_audit.occurred_on
 13        );
 14
 15     IF employee_audit.description LIKE 'AFTER%'
 16     THEN
 17        RAISE VALUE_ERROR;
 18     END IF;
 19
 20     COMMIT;
 21  END;
 22  /

Procedure created.

SQL> CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
  2  BEFORE INSERT ON employee_compensation FOR EACH ROW
  3  DECLARE
  4     ok BOOLEAN := TRUE;
  5  BEGIN
  6     employee_audit (
  7        :new.name, 'BEFORE INSERT', SYSDATE);
  8  END;
  9  /

Trigger created.

SQL>
SQL> CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
  2  AFTER INSERT ON employee_compensation FOR EACH ROW
  3  DECLARE
  4     ok BOOLEAN := FALSE;
  5  BEGIN
  6     employee_audit (
  7        :new.name, 'AFTER INSERT', SYSDATE);
  8  END;
  9  /

Trigger created.

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

no rows selected

SQL>
SQL> BEGIN
  2     INSERT INTO employee_compensation VALUES ('M', 'J', 9100000, 2700);
  3
  4     INSERT INTO employee_compensation VALUES ('A', 'H', 33200000, 3300);
  5
  6     INSERT INTO employee_compensation VALUES ('E', 'G', 10700000, 20100);
  7
  8     ROLLBACK; -- I wish!
  9  END;
 10  /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "JAVA2S.EMPLOYEE_AUDIT", line 17
ORA-06512: at "JAVA2S.AFT_INS_CEO_COMP", line 4
ORA-04088: error during execution of trigger 'JAVA2S.AFT_INS_CEO_COMP'
ORA-06512: at line 2


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

NAME                 DESCRIPTION                    OCCURRED_ON
-------------------- ------------------------------ -------------------
J                    BEFORE INSERT                  07/24/2008 08:03:13

SQL>
SQL>
SQL>
SQL>
SQL> DROP TABLE employee_history;

Table dropped.

SQL>
SQL> DROP TABLE employee_compensation;

Table dropped.

SQL>