Are you interested in accessing columns in a row level trigger using a for loop?
I have been talking to someone from Oracle via email recently. So if enough people express an interest in adding a new feature to Oracle, perhaps in a future version of Oracle, this enhancement might be available.
Currently in PL/SQL there are two metadata rows, :OLD, and :NEW. If you want to refer to a column you simply refer to it as :NEW.columnname or :OLD.columnname depending on whether you want the :NEW or :OLD value for this column. Therefore if you want to log the change in every column in a given table, you reference every column and either do an if statement comparing the values and then calling a logging procedure, or just call a procedure and have it do the comparison, and if necessary the logging.
Would it be helpful to just create a simple for loop that would iterate through each column in the table, without specifying a single column name, and then performing some function, i.e. calling the logging procedure. You would of course have to overload your logging procedure for every data type you planned on using in that table, i.e. VARCHAR2, NUMBER, DATE, etc. It might also be helpful to have access to the datatype, so that you can and an if statement telling the for loop to skip LONG, RAW, BLOBS, etc.
So instead of:
Code:
calllogger('col1',:NEW.col1, :OLD.col1);
calllogger('col2',:NEW.col2, :OLD.col2);
calllogger('col3',:NEW.col3, :OLD.col3);
calllogger('col4',:NEW.col4, :OLD.col4);
etc...
You would simply:
Code:
FOREACH col IN metacolumns
LOOP
calllogger(col.comunname, col.newvalue, col.oldvalue
END LOOP;
