RE: :new and :old

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 17 Jun 2005 09:52:24 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF41050395B94D@usahm236.amer.corp.eds.com>

Ken, your problem in this code is that there is no :old or :new variable for the cursor which is on your table that defines what tables and columns you want to audit. The :old and :new references are for the row data columns being modified. You need to substitute the actual trigger table column name into the insert at compile time to use static SQL. Otherwise you have to resort to building the insert of the audit information dynamically.

You can do this using either execute immediate or dbms_sql; however, dynamic SQL is expensive compared to static SQL. This design is not efficient and will result in a large amount of dynamic SQL being ran on your system.

Instead write a routine to read your table/column audit driver table and generate the triggers which you then apply. If you make this a package owned by a DBA privileged user then you can put a screen in front of it and when changes are made to the driver table automatically regenerate and apply the updated trigger.

Basically if you write one insert, update, and delete audit trigger then the only thing that changes from one table to another is the table and column names. So writing a routine to generate the code and substitute in the correct table and column names is pretty easy. This method will give you what you need using sharable SQL.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kean Jacinta Sent: Friday, June 17, 2005 6:35 AM
To: Igor Neyman; oracle-l_at_freelists.org
Subject: RE: :new and :old

hi ,

This is my trigger code. Basically this trigger is not working. I can't figure out how to do it. Can someone give me some example

CREATE OR REPLACE TRIGGER "CHARMS_ADM"."EMP_AUDIT" AFTER INSERT OR UPDATE ON "EMP" FOR EACH ROW DECLARE

  V_AUDITTABLENAME     VARCHAR2(50);
  V_TABLENAME        VARCHAR2(50);
  V_COUNT            NUMBER;

CURSOR C1 IS
SELECT AUDITFIELDNAME

      FROM AUDITTRACKFLD 
     WHERE AUDITTABLENAME = V_TABLENAME;

BEGIN

CHECK IF THIS TABLE NEEDS TO BE TRACK

SELECT TABLE_NAME

      INTO V_TABLENAME
      FROM USER_TRIGGERS    
     WHERE TRIGGER_NAME = 'EMP_AUDIT';
        
    SELECT COUNT(*)
      INTO V_COUNT
      FROM AUDITTRACKTBL
     WHERE AUDITTABLENAME = V_TABLENAME;

  IF INSERTING THEN
  --PICK TABLE TO TRACK
  --PICK FIELD TO TRACK

TABLE NEED TO BE AUDITED
IF V_COUNT <> 0 THEN

--WHICH FIELD TO AUDIT

      
      FOR C1_REC IN C1
      LOOP
     
      INSERT INTO EMPAUDITTRAIL_TBL
(AUDITUSERID,AUDITEMPLOYEENAME,AUDITDATETIME,
      AUDITCHANGETYPE,AUDITTABLENAME,AUDITFIELDNAME,
      AUDITKEYVALUE,AUDITBEFOREVALUE,AUDITAFTERVALUE) 
            
      VALUES
(:NEW.CREATEDBY,:NEW.EMPLOYEENAME,:NEW.CREATEDDATE,
      'INSERT',V_TABLENAME,C1_REC.AUDITFIELDNAME,
     
'NOPRIMARYKEY',:OLD.C1_REC.AUDITFIELDNAME,:NEW.C1_REC.AUDITFIELDNAME);
      
      END LOOP;

END IF; END IF; END;

Discover Yahoo!
Have fun online with music videos, cool games, IM and more. Check it out!
http://discover.yahoo.com/online.html

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 17 2005 - 09:58:24 CDT