0

I have a table with corresponding AFTER UPDATE trigger for changes auditing (Oracle 11g). Table Test01 to be audited:

CREATE TABLE Test01 
(
    Name Varchar2(40),
    Surname Varchar2(40) 
);

and table History01 for auditing of changes

CREATE TABLE History01 
(
    Old_val Varchar2(40),
    New_val Varchar2(40), 
    Changed_col Varchar2(40)
);

It works fine when I use column names with :old and :new aliases, like:

create or replace TRIGGER trg_history01
  AFTER UPDATE ON Test01
  FOR EACH ROW
BEGIN
  if :new.Name != :old.Name or ( :old.Name is NULL and :new.Name is not NULL ) then
      INSERT INTO History01 VALUES (:old.Name, :new.Name, 'Name');
  if :new.Surname != :old.Surname or ( :old.Surname is NULL and :new.Surname is not NULL ) then
      INSERT INTO History01 VALUES (:old.Surname, :new.Surname, 'Surname');
end if  
  end if;
END;

Test01 could contain more columns and so I would like to iterate over all columns instead of to use column names. But I don't know how to do it, because folowing code does not work (cannot be compiled):

create or replace TRIGGER trg_history01
  AFTER UPDATE ON Test01
  FOR EACH ROW
DECLARE
  v_old varchar2(255);
  v_new varchar2(255);
  -- col_names contains all column names from table Test01
  CURSOR col_names 
  IS 
  ( SELECT column_name FROM USER_TAB_COLUMNS 
    WHERE LOWER(table_name) = 'test01' );
BEGIN
  for col in col_names LOOP
    -- save column name into variable
    col_name := col.column_name; 
    -- to use it with OLD and NEW aliases, but followig two lines cause error "Bad bind variable"
    v_old := :OLD.col_name; 
    v_new := :NEW.col_name;
    if v_old != v_new or ( v_old is NULL and v_new is not NULL ) then
      INSERT INTO History01 VALUES ( v_old, v_new, col_name);
    end if;
  END LOOP;
END;

Is there a way how to do it or I always have to use column names?

APC
  • 144,005
  • 19
  • 170
  • 281
Lukas
  • 2,034
  • 19
  • 27
  • 1
    There is no way to solve this in PL/SQL, unless you use dynamic SQL to build and execute a dynamic function, which is a risky approach (Dynamic SQL in Production is always risky.). The problem is the structure of your `history01` table. This is a very common anti-pattern. Storing old and new values in a row seems like a good idea but it isn't. There's the loss of datatype, there's the loss of structure, there's the additional overhead of inserting multiple rows of audit data for one changed row. – APC Dec 29 '19 at 11:57
  • 1
    So, don't do this. Build a `history01` table which has the same projection as `test01` plus necessary metadata columns (transaction timestamp, dml action, etc). In the trigger insert a record of `:old` values (or `:new` values if that's your taste) into the audit table. The trigger can be generated from the data dictionary. – APC Dec 29 '19 at 12:00
  • Also, don't build your own auditing system when Oracle bundle Flashback Data Archive, their journalling capability, free with all editions since 11.2.0.4. [Find out more](https://oracle-base.com/articles/12c/flashback-data-archive-fda-enhancements-12cr1) – APC Dec 29 '19 at 12:12

0 Answers0