Re: Audit Trigger

From: Robert Fischer <robert_fischer_at_gmx.de>
Date: Wed, 24 Oct 2001 20:07:45 +0200
Message-ID: <4q0ett0o0ihj7hotq6vf2avpb5esn7gepc@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...

  end loop;
  close get_cols;
end;
/
show errors;
commit;

I want to have only upper cases in the database and I don't know the column names - perhaps there is another solution?

Gruß Robert

-- 
sig

Received on Wed Oct 24 2001 - 13:07:45 CDT