Votes

Better Access to New/Old Data In Triggers (details)
 
Dear Bryn,

Database triggers are a powerful mechanism for ensuring data integrity and other table-level rules. And I like the way that :NEW and :OLD give me access to the column level information.

It would be so much better, though, if I could access runtime data structures, similar to SQL%BULK_EXCEPTIONS, so that I could write more flexible and generic trigger management code.


The :NEW and :OLD pseduo-records are very useful, but the limitations on their usage are a hassle. You cannot pass them as arguments to generic logging programs, and you have to write hard-coded logic to reference individual columns in the table.


A much more flexible approach would provide us with runtime data structures, such as a pseudo-collection like SQL%BULK_EXCEPTIONS, which contains all of the desired information, and can be passed as an argument to a program.


begin
  CheckAndLog('Table',:New.ID, 'FIELDx',
                     :Old.Fieldx, :New.Fieldx);
  CheckAndLog('Table',:New.ID, 'FIELDx',
                     :Old.Fieldx, :New.Fieldx);
  CheckAndLog('Table',:New.ID, 'FIELDx',
                     :Old.Fieldx, :New.Fieldx);
  CheckAndLog('Table',:New.ID, 'FIELDx',
                     :Old.Fieldx, :New.Fieldx);
  CheckAndLog('Table',:New.ID, 'FIELDx',
                     :Old.Fieldx, :New.Fieldx);
  CheckAndLog('Table',:New.ID, 'FIELDx',
                     :Old.Fieldx, :New.Fieldx);
...
End;


DECLARE
   /* Emulate the basic idea: a pseudo collection of records, in which
      each record contains information about the column in the table -
      the old and new values, has it been changed, etc.
      
      Then SQL%COLUMNS and SQL%TRIGGER_TABLE provide access to this 
      information at runtime, inside the trigger.
   */
   TYPE trig_column_info_rt IS RECORD (
      NAME         VARCHAR2 (30)
    , old_value    INTEGER-- ,
      new_value    INTEGER-- ,
      is_changed   BOOLEAN
   );

   TYPE trig_columns_t IS TABLE OF trig_column_info_rt
      INDEX BY PLS_INTEGER;

   SQL%COLUMNS   trig_columns_t;
BEGIN
   FOR indx IN 1 .. SQL%COLUMNS.COUNT
   LOOP
      IF SQL%COLUMNS (indx).ischanged
      THEN
         CheckAndLog(SQL%TRIGGER_TABLE, SQL%COLUMNS (indx));
      END IF;
   END LOOP;
END;


Steven Feuerstein, PL/SQL Evangelist, Quest Software: "Help me help Oracle improve the PL/SQL language!"


Bryn Llewellyn: "We love to hear from PL/SQL developers. Let us know what is important to you!"

PL/SQL Obsession
Apex Evangelists
O'Reilly Books on Oracle
OTN PL/SQL Best Practices
OTN PL/SQL Page
Steven Feuerstein's Blog