RE: :new and :old
- From: Kean Jacinta <jacintakean@xxxxxxxxx>
- To: Thomas.Mercadante@xxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 16 Jun 2005 08:59:06 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Discover Yahoo!
Use Yahoo! to plan a weekend, have fun online & more. Check it out!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
