Re: :new and :old
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: jacintakean@xxxxxxxxx
- Date: Fri, 17 Jun 2005 11:51:35 +0100
Hi Jacinta
I think that I would be sorely tempted to audit *all* the changes, and use
the master table at reporting time to decide what I wish to report on. I'm
pretty sure that your approach (dynamically build an audit strategy),
particularly if you are potentially doing this for all tables in your schema
will lead to you missing something that you wish to audit at a later date.
cheers
On 6/16/05, Kean Jacinta <jacintakean@xxxxxxxxx> wrote:
>
> 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 <http://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
>
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Other related posts:
