Mega Code Archive
Commit in trigger
<![CDATA[
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 "RNTSOFT.AFT_INS_CEO_COMP", line 15
ORA-04088: error during execution of trigger 'RNTSOFT.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> DROP TABLE employee_compensation;
Table dropped.
SQL>
SQL> DROP TABLE employee_history;
Table dropped.
SQL>
]]>