(ref) Trigger on Object Table

You can create triggers on object tables. In this case, the trigger can reference the pseudocolumn 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 in emp_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:
obj_employee is an object table. Each of its rows is an object of the type EmployeeType emp_hist is a traditional table. Two of its columns are objects of the type EmployeeType (?) How is the translation/mapping OO-Relational done?	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 in emp_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>

Back to Triggers