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

Other related posts: