RE: :new and :old
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <jacintakean@xxxxxxxxx>, <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Jun 2005 12:18:20 -0400
Posting actual code of your trigger will do better explanation of what
you are trying to achive.
Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kean Jacinta
Sent: Thursday, June 16, 2005 10:59 AM
To: Thomas.Mercadante@xxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: :new and :old
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
