OBJECT_VALUE.
In the example below you:
(1) Create an
OBJECT_TYPE
to define a table.(2) Create an
OBJECT TABLE
obj_employee
(3) Insert data on
obj_employee
(4) Create a table
emp_hist
for logging updates to obj_employee
(5) Create a trigger
emp_obj_trg
. - The trigger will execute for each row of
obj_employee
affected by a DML statement. - The old and new values of the object emp in
obj_employee
will be written inemp_history
. - The old and new values are
:OLD.OBJECT_VALUE
and:NEW.OBJECT_VALUE
(1-3) Create
OBJECT_TYPE
, OBJECT TABLE
and Insert data.SQL> CREATE or REPLACE TYPE EmployeeType AS OBJECT ( Id number, first_name varchar2(15), last_name varchar2(15), hire_date date, salary number(8,2) ); Type created. SQL> CREATE TABLE obj_employee OF EmployeeType; Table created. SQL> INSERT INTO obj_employee VALUES ( EmployeeType(1, 'John', 'Martic', '02-AUG-03', 95000)) 1 row created. SQL> INSERT INTO obj_employee VALUES ( EmployeeType(2, 'Mary', 'Kempft', '02-JUL-99', 98000))) 1 row created. SQL> INSERT INTO obj_employee VALUES ( EmployeeType(3, 'Greg', 'Bloom', '02-AUG-09', 55000)) 1 row created. SQL> commit; Commit complete. SQL> select * from obj_employee; ID FIRST_NAME LAST_NAME HIRE_DATE SALARY ------ --------------- --------------- ------------ ---------- 1 John Martic 02-AUG-03 95000 2 Mary Kempft 02-JUL-99 98000 3 Greg Bloom 02-AUG-09 55000
(4) Create history table
emp_hist
:SQL> CREATE TABLE emp_hist (dt date, username varchar2(20), old_obj_emp EmployeeType, new_obj_emp EmployeeType); table created. SQL>
Compare emp_hist and obj_employee : |
|
---|---|
| SQL> desc obj_employee Name Null? Type ------------------ ------ ------------------ ID NUMBER FIRST_NAME VARCHAR2(15) LAST_NAME VARCHAR2(15) HIRE_DATE DATE SALARY NUMBER(8,2) SQL> desc emp_hist Name Null? Type ------------------ ------ ------------------ DT DATE USERNAME VARCHAR2(20) OLD_OBJ_EMP EMPLOYEETYPE NEW_OBJ_EMP EMPLOYEETYPE |
(5) Create a trigger
emp_obj_trg
. - The trigger will execute for each row of
obj_employee
affected by a DML statement. - The old and new values of the object emp in
obj_employee
will be written inemp_history
. - The old and new values are
:OLD.OBJECT_VALUE
and:NEW.OBJECT_VALUE
SQL> CREATE OR REPLACE TRIGGER emp_obj_trg AFTER UPDATE on obj_employee FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('Logging Update'); INSERT INTO emp_hist (dt, username, old_obj_emp, new_obj_emp) VALUES (sysdate, user, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE); END emp_obj_trg; Trigger created. SQL> SQL> set serveroutput on SQL> update obj_employee set salary=salary*1.10 where id=1; Logging Update 1 row updated. SQL> update obj_employee set salary=salary*.98; Logging Update Logging Update Logging Update 3 rows updated.
(6) Check the audit table (using implicit cursor):
SQL> set serveroutput on SQL> BEGIN for j in (select * from emp_hist) loop dbms_output.put_line ( 'Date: ' || j.dt ||'-- User '|| j.username); DBMS_OUTPUT.PUT_LINE( '(old rec)-- ID:'|| j.old_obj_emp.id || '-- OLD SALARY: '|| j.old_obj_emp.salary); DBMS_OUTPUT.PUT_LINE( '(new rec)-- ID:'|| j.new_obj_emp.id || '-- NEW SALARY: '|| j.new_obj_emp.salary); end loop; end; / Date: 22-AUG-11-- User FHAT (old rec)-- ID:1-- OLD SALARY: 95000 (new rec)-- ID:1-- NEW SALARY: 104500 Date: 22-AUG-11-- User FHAT (old rec)-- ID:1-- OLD SALARY: 104500 (new rec)-- ID:1-- NEW SALARY: 102410 Date: 22-AUG-11-- User FHAT (old rec)-- ID:2-- OLD SALARY: 98000 (new rec)-- ID:2-- NEW SALARY: 96040 Date: 22-AUG-11-- User FHAT (old rec)-- ID:3-- OLD SALARY: 55000 (new rec)-- ID:3-- NEW SALARY: 53900 PL/SQL procedure successfully completed. SQL>