Oracle7 triggers question.

From: Miroslaw Piaseczny <mirek_at_unixg.ubc.ca>
Date: 20 Feb 1993 00:27:37 GMT
Message-ID: <1m3ttpINN6kn_at_skeena.ucs.ubc.ca>

I'm just trying to put together a small application where I can make use of the database triggers. My question is:  

How can I reference the :old.colname, and :new.colname so that the colnames are not used explicitly. Can I used something like NAME_IN packaged function found in forms3?

The following is a descripion of the tables:

SQL> describe medication;

 Name                            Null?    Type
 ------------------------------- -------- ----
 MED_CODE                        NOT NULL NUMBER(2)
 MED_NAME                                 VARCHAR2(24)


SQL> describe audit_tbl;
 Name                            Null?    Type
 ------------------------------- -------- ----
 MODDATE                                  DATE
 MODCODE                                  VARCHAR2(1)
 TABLENAME                                VARCHAR2(12)
 FIELDNAME                                VARCHAR2(12)
 OLDVALUE                                 VARCHAR2(255)
 NEWVALUE                                 VARCHAR2(255)
 USERID                                   VARCHAR2(10)

The following statement creates trigger to capture all updates done on the 'medication' table and stores that in the 'audit_tbl':  

CREATE OR REPLACE TRIGGER change_audit_med AFTER UPDATE ON medication
FOR EACH ROW BEGIN   IF UPDATING ('med_code') THEN
    INSERT INTO audit_tbl

       VALUES (SYSDATE,'M','MEDICATION','MED_CODE',:old.med_name,


               :new.med_name,USER);

  END IF;   IF UPDATING ('med_name') THEN
    INSERT INTO audit_tbl

       VALUES (SYSDATE,'M','MEDICATION','MED_NAME',:old.med_name,


               :new.med_name,USER);

  END IF; END; I would like to replace the above triger with something like this:    

      CREATE OR REPLACE TRIGGER change_audit_med
      AFTER UPDATE ON medication
      FOR EACH ROW 
 
      DECLARE
        CURSOR COL_LIST IS
                SELECT COLUMN_NAME
                FROM ALL_TAB_COLUMNS
                WHERE TABLE_NAME='MEDICATION'
                AND OWNER='MIREK7';
 
        OLD_VAL varchar2(100);
        NEW_VAL varchar2(100);
        COL_NM  varchar2(100);
BEGIN
     
        FOR COL_INST IN COL_LIST LOOP
 
        COL_NM := COL_INST.COLUMN_NAME;
 
        IF UPDATING (COL_NM) THEN
 
          OLD_VAL := 'OLD.'||COL_NM;
          NEW_VAL := 'NEW.'||COL_NM;
 
 
          INSERT INTO audit_tbl
             VALUES (SYSDATE,'M','MEDICATION',COL_NM,
                        NAME_IN(OLD_VAL),NAME_IN(NEW_VAL),USER);
                        ^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^^^^
        END IF;
        END LOOP;

END; That way the trigger would be more general, and also wouldn't depent on the number of columns in the 'medication' table.

Any comments would be appreciated,
Thanks,

Mirek Piaseczny
University of British Columbia
mirek_at_unixg.ubc.ca Received on Sat Feb 20 1993 - 01:27:37 CET