|
SQL>
SQL> CREATE TABLE employee_history (
2 name VARCHAR2(100),
3 description VARCHAR2(255),
4 occurred_on DATE);
Table created.
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 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 COMMIT;
15 END;
16 /
Procedure created.
SQL>
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 IF ok
7 THEN
8 employee_audit (
9 :new.name, 'AFTER INSERT', SYSDATE);
10 ELSE
11 RAISE VALUE_ERROR;
12 END IF;
13 END;
14 /
Trigger created.
SQL>
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 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "JAVA2S.AFT_INS_CEO_COMP", line 9
ORA-04088: error during execution of trigger 'JAVA2S.AFT_INS_CEO_COMP'
ORA-06512: at line 2
SQL>
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>
SQL>
SQL> DROP TABLE employee_compensation;
Table dropped.
SQL>
SQL> DROP TABLE employee_history;
Table dropped.
SQL>
|