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_employeeaffected by a DML statement. - The old and new values of the object emp in
obj_employeewill be written inemp_history. - The old and new values are
:OLD.OBJECT_VALUEand: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_employeeaffected by a DML statement. - The old and new values of the object emp in
obj_employeewill be written inemp_history. - The old and new values are
:OLD.OBJECT_VALUEand: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>
