|
SQL> CREATE TABLE employee_history (
2 name VARCHAR2(100),
3 description VARCHAR2(255),
4 occurred_on DATE);
Table created.
SQL>
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 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>
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 IF ok
7 THEN
8 employee_audit (
9 :new.name, 'BEFORE INSERT', SYSDATE);
10 END IF;
11 END;
12 /
Trigger created.
SQL>
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 /
PL/SQL procedure successfully completed.
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:08
H
BEFORE INSERT
07/24/2008 08:03:08
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
OCCURRED_ON
-------------------
G
BEFORE INSERT
07/24/2008 08:03:08
SQL>
SQL>
SQL> DROP TABLE employee_compensation;
Table dropped.
SQL>
SQL> DROP TABLE employee_history;
Table dropped.
|