View Poll Results: Are you interested in this potential new feature?
- Voters
- 6. You may not vote on this poll
-
Yes, I am interested and would use such a feature if available.
-
Yes, I am interested, but I'm not sure if I would use it.
-
Not sure, I would need to see some examples.
-
No, I would rather not use such a feature.
-
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;
-
The same idea is explained in a different way on Steven Feuerstein's website.
http://iloveplsqland.net/pls/apex/f?...F37E6DAAD6C093
or
http://iloveplsqland.net/pls/apex/f?...0CB642A6D6D91C
While you are at it look at the other ideas and vote for any others that interest you.
http://iloveplsqland.net/pls/apex/f?...20059965065207
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|