RE: :new and :old

From: Kean Jacinta <jacintakean_at_yahoo.com>
Date: Fri, 17 Jun 2005 03:34:35 -0700 (PDT)
Message-ID: <20050617103435.95429.qmail@web52901.mail.yahoo.com>

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

Received on Fri Jun 17 2005 - 06:39:43 CDT