Need help with trigger to compare columns dynamically

I don't know if this is possible or not and to date I haven't been able to figure this out and haven't found anything on the FAQ's that would answer my question. I'm also new to Oracle and am not sure of what its limitations/strengths are so please bear with me.

I want to build a 'generic' trigger that I can create on several tables without having to make major modifications. It needs to figure out and concatenate the PK (which it does) and then read all the column names for that table and compare the old and new values (the values part is what I'm having problems with).

I've included a simple example below to show what I'm currently trying to do. The variable COLUMN_NAME will hold the actual name of the column (last_name) from the table the trigger is firing on.

cursor get_columns ...
open ...
loop

   fetch get_columns into COLUMN_NAME;
   if (:new.COLUMN_NAME <> :old.COLUMN_NAME) then ...    

When I try to create this trigger I get an error message stating that COLUMN_NAME in the 'IF' stmt is an invalid column. I didn't think this would work but was hoping COLUMN_NAME would get resolved.

Anyone have an idea as to how to compare the old and new values without having to explicitly type IF (:new.last_name <> :old.last_name)...

Thanks in advance. Received on Thu Jan 23 1997 - 00:00:00 CST