Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Audit Trigger

Re: Audit Trigger

From: Sergey M <msu_at_pronto.msk.ru>
Date: Thu, 25 Oct 2001 12:09:59 +0400
Message-ID: <9r8hcv$m5g$1@serv2.vsi.ru>

"Robert Fischer" <robert_fischer_at_gmx.de> сообщил/сообщила в новостях следующее: news:4q0ett0o0ihj7hotq6vf2avpb5esn7gepc_at_4ax.com...
> Am Fri, 24 Aug 2001 10:04:55 +0100 schrieb Connor McDonald
> <connor_mcdonald_at_yahoo.com> in <3B8618B7.33FE_at_yahoo.com>:
>
> Hallo Connor McDonald:
>
> >> create or replace trigger audit_trigger
> >> after update or insert ot delete on employees for each row
> >>
> >> declare
> >> cursor aud_cur is select column_name , column_type from
user_tab_columns
> >> where table_name = 'employees';
> >>
> >> begin
> >> if updating then
> >> for aud_row in aud_cur loop
> >> ------here is my problem -----
> >> if :old.aud_row.column_name <> :new.aud_row.column_name
then
> >> insert into audit_table values
(:old.aud_row.column_name,
> >> :new.aud_row.column_name)
> >>
> >> --- bla, bla, bla
> >
> >You need another layer of abstraction, ie, write a little script that
> >will generate all the trigger code (using user_tab_columns to do it) for
> >each table that you want to monitor.
> >
> I have the same problem and I didn't understand your solution.
> My problem is the following:
>
> create or replace trigger
> before update or insert on A_TESTTABLE_LEER
> for each row
> declare cursor get_cols is
> select column_name
> from user_tab_columns
> where table_name = 'A_TESTTABLE_LEER';
> col_record get_cols%rowtype;
> begin
> open get_cols;
> loop
> fetch get_cols into col_record;
> :new.col_record.column_name := upper(:new.col_record.column_name);
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^-here is the problem...

You don't use NEW for col_record, because :NEW is A_TESTTABLE_LEER%ROWTYPE, and :NEW is new values for each column.
I don't understand you. What do you want to do this trigger?

Sergey M. Received on Thu Oct 25 2001 - 03:09:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US