Best Practices for Managing Old and New Information

Question:
Hello Steven, My question is about Oracle Triggers. Here is the problem. My purpose is to audit updates so i want to store before and after value of any column of a specified table. In order to achieve this i used :new and :old structures with hard coded column names. But this cause difficulties while adding and dropping columns. i want something more dynamic without coding column names directly. if it is possible, could you help me? current implementation Table definition: CREATE TABLE LOAD_A ( A1 VARCHAR2 (10), A2 VARCHAR2 (10) ) ; create or replace trigger ins_load_a AFTER update ON SCOTT.load_a FOR EACH ROW declare a_rec scott.load_a%rowtype; BEGIN a_rec.a1 := :old.a1; -- (1) a_rec.a2 := :old.a2; -- (2) save_old_values(a_rec); END; thanks.
Answer:
The bad news is that up through Oracle Database 10g, you cannot pass :OLD and :NEW as arguments to procedures. The good news is that at least you don't have to write all the code needed to accomplish your goal.
Let's take a step back: you want to pass the old and new values available inside a trigger to a stored procedure to process the data (write the information to a log table, execute validation logic, and so on). You cannot pass :OLD and :NEW as records; instead, you must copy the individual fields of those pseudorecords to a real record that can be passed to the procedure.
It certainly is tedious and time-consuming to write that code yourself, especially for tables with many columns. Fortunately, it is quite easy to generate the code you desire.
I offer you the gen_audit_trigger_text procedure shown in Listing 1 to help you achieve your goal. I ran this program for the employees table and, after some formatting, had the resulting code shown in Listing 2.
Code Listing 1: gen_audit_trigger_text
CREATE OR REPLACE PROCEDURE gen_audit_trigger_text (
table_in IN VARCHAR2
, owner_in IN VARCHAR2 := USER
, program_name_in IN VARCHAR2 := 'process_data'
)
IS
c_rowtype CONSTANT VARCHAR2 (100) := table_in || '%ROWTYPE';
l_columns DBMS_SQL.varchar2s;

PROCEDURE gen_copy_proc (old_or_new_in IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line ( 'FUNCTION copy_'
|| old_or_new_in
|| ' RETURN '
|| c_rowtype
|| ' IS l_return '
|| c_rowtype
|| '; BEGIN '
);

FOR indx IN 1 .. l_columns.COUNT
LOOP
DBMS_OUTPUT.put_line ( ' l_return.'
|| l_columns (indx)
|| ' := '
|| ':'
|| old_or_new_in
|| '.'
|| l_columns (indx)
|| ';'
);
END LOOP;

DBMS_OUTPUT.put_line ('RETURN l_return;');
DBMS_OUTPUT.put_line ('END copy_' || old_or_new_in || ';');
END gen_copy_proc;
BEGIN
SELECT LOWER (column_name) column_name
BULK COLLECT INTO l_columns
FROM all_tab_columns
WHERE owner = UPPER (owner_in) AND table_name = UPPER (table_in);

DBMS_OUTPUT.put_line ('DECLARE');
DBMS_OUTPUT.put_line (' my_Old ' || table_in || '%ROWTYPE;');
DBMS_OUTPUT.put_line (' my_New ' || table_in || '%ROWTYPE;');
gen_copy_proc ('old');
gen_copy_proc ('new');
DBMS_OUTPUT.put_line ('BEGIN');
DBMS_OUTPUT.put_line (' my_Old := copy_Old ();');
DBMS_OUTPUT.put_line (' my_New := copy_New ();');
DBMS_OUTPUT.put_line (' ' || program_name_in || '(my_Old, my_new);');
DBMS_OUTPUT.put_line ('END;');
END gen_audit_trigger_text;
/
Code Listing 2: Result of gen_audit_trigger_text procedure on employees table
DECLARE
my_old employees%ROWTYPE;
my_new employees%ROWTYPE;

FUNCTION copy_old
RETURN employees%ROWTYPE
IS
l_return employees%ROWTYPE;
BEGIN
l_return.employee_id := :OLD.employee_id;
l_return.first_name := :OLD.first_name;
l_return.last_name := :OLD.last_name;
l_return.email := :OLD.email;
l_return.phone_number := :OLD.phone_number;
l_return.hire_date := :OLD.hire_date;
l_return.job_id := :OLD.job_id;
l_return.salary := :OLD.salary;
l_return.commission_pct := :OLD.commission_pct;
l_return.manager_id := :OLD.manager_id;
l_return.department_id := :OLD.department_id;
RETURN l_return;
END copy_old;

FUNCTION copy_new
RETURN employees%ROWTYPE
IS
l_return employees%ROWTYPE;
BEGIN
l_return.employee_id := :NEW.employee_id;
l_return.first_name := :NEW.first_name;
l_return.last_name := :NEW.last_name;
l_return.email := :NEW.email;
l_return.phone_number := :NEW.phone_number;
l_return.hire_date := :NEW.hire_date;
l_return.job_id := :NEW.job_id;
l_return.salary := :NEW.salary;
l_return.commission_pct := :NEW.commission_pct;
l_return.manager_id := :NEW.manager_id;
l_return.department_id := :NEW.department_id;
RETURN l_return;
END copy_new;
BEGIN
my_old := copy_old ();
my_new := copy_new ();
process_data (my_old, my_new);
END;
You should be able to enhance gen_audit_trigger_text in a straightforward way to also generate the header of your trigger; I leave that as an exercise for the reader.