Oracle before update trigger - dynamically loop through columns with old and new value

654 Views Asked by At

I'm writing a database trigger for a history function.

I had the hope that there is a chance to use one trigger for multiple tables. Also I don't want to check every single column by name.

So I tried to create the trigger using dynamical SQL like this:

  FOR columnItem IN (SELECT column_name 
                        FROM all_tab_columns 
                       WHERE TABLE_NAME = UPPER(tableName))
   LOOP
   -- how to use columnItem.column_name for the loop
   -- like :new.[columnItem.column_name] or something like that
       if :new.NAME != :old.NAME Then
         INSERT INTO HISTORY 
         VALUES (HIS_SEQUENCE.nextval, tableName, :new.LOCAL_ID, columnItem.column_name, 'OLD', 'NEW', sysdate(), 'STACKOVERFLOW');
      End If;
   END LOOP;

That's not possible, is it? Hope you know a way to do it.

Thanks in advance.

0

There are 0 best solutions below