RE: :new and :old

From: Kean Jacinta <jacintakean_at_yahoo.com>
Date: Thu, 16 Jun 2005 08:59:06 -0700 (PDT)
Message-ID: <20050616155907.48732.qmail@web52910.mail.yahoo.com>

Hi,

Yes i am trying to create a trigger that track changest made on employee_tbl.Our database is created in such a way that all the table and fieldname that need to be track will be stored in AUDITTABFLD table. This audit table field contains , tablename and fieldname that needs to be audited.

If the trigger is triggered by a INSERT/UPDATE statement on employee_tbl , then it will read audittabfld.To see if this table needs to be audited. If in this case, the table existed in AUDITTABFLD then the trigger will have to retrieve all the fieldname to be audited, and log all the field name before and after value into EMPLOYEE_LOG_TBL. So in my triggers i have created a cursor to read the fieldname and then loop the statement.

I encounter some problem in assigning the OLD and NEW value. Error will occur then i tried to do this :OLD.<fieldname> :NEW.<fieldname>

Since the cur1 will return more than one value , then i need it to be dynamic.

I hope you can understand what i am trying to explain here.:)

THANK YOU
JKEAN i have declare a cursor in my trigger called cur1. I am trying to refer :OLD.
:NEW. from the cursor. I got an
error=20

PLS-00049 bad-bind variable. I am not sure if oracle support this. If this way is not gonna work then what can you pls suggest other method to acheive the same result.=20

THank YOU
JKean

=20

Declare
CURSOR cur1 IS
SELECT FIELDNAME=20
FROM TRACKFLD=20
WHERE TRACKTABLENAME =3D 'COMPANY_MASTER' Begin
FOR cur1_rec IN cur1

LOOP
=20

INSERT INTO AUDITLOG =20
(FIELDNAME,AUDITBEFOREVALUE,AUDITAFTERVALUE)=20
=20

VALUES
(:FIELDNAME,:OLD.cur1_rec.FIELDNAME,:NEW.cur1_rec.FIELDNAME);
=20

END LOOP;
=20

=09

__________________________________=20

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

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


		
---------------------------------
Discover Yahoo!
 Use Yahoo! to plan a weekend, have fun online & more. Check it out!

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

Received on Thu Jun 16 2005 - 12:15:04 CDT