Mega Code Archive
Mark trigger with PRAGMA AUTONOMOUS_TRANSACTION
<![CDATA[
SQL>
SQL> CREATE TABLE employee_compensation (
2 company VARCHAR2(100),
3 name VARCHAR2(100),
4 compensation NUMBER,
5 layoffs 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 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 "RNTSOFT.EMPLOYEE_AUDIT", line 17
ORA-06512: at "RNTSOFT.AFT_INS_CEO_COMP", line 4
ORA-04088: error during execution of trigger 'RNTSOFT.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> DROP TABLE employee_history;
Table dropped.
SQL>
SQL> DROP TABLE employee_compensation;
Table dropped.
SQL>
]]>