Are you interested in accessing columns in a row level trigger using a for loop?
DBAsupport.com Forums - Powered by vBulletin

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.

    3 50.00%
  • Yes, I am interested, but I'm not sure if I would use it.

    3 50.00%
  • Not sure, I would need to see some examples.

    0 0%
  • No, I would rather not use such a feature.

    0 0%
Results 1 to 2 of 2

Thread: Are you interested in accessing columns in a row level trigger using a for loop?

  1. #1
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021

    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;
    this space intentionally left blank

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width