RE: :new and :old
- From: Kean Jacinta <jacintakean@xxxxxxxxx>
- To: Igor Neyman <ineyman@xxxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 17 Jun 2005 03:34:35 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
