Re: Audit Trigger

Am Thu, 25 Oct 2001 12:09:59 +0400 schrieb "Sergey M" <msu_at_pronto.msk.ru> in <9r8hcv$m5g$1_at_serv2.vsi.ru>:

Hallo Sergey M:

>> 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?

If the user is adding or changing a dataset, I want to write it to the database in upper cases.
for example he is changing an entry from EFG001EEL
to
EFG001ggL
but I want to write to the database EFR001GGL <- big letters.

The reason to use a trigger is that I can't know which title the columns have and how much there are.

If I would know all the columns I could write:

CREATE OR REPLACE TRIGGER TR_TESTTABLE_LEER before INSERT or UPDATE on A_TESTTABLE_LEER for each row
begin

  :new.ColumnName_1 := upper(:new.ColumnName_1);
  :new.ColumnName_2 := upper(:new.ColumnName_2);
    ...
  :new.ColumnName_X := upper(:new.ColumnName_X);

Do you understand what I mean?

(sorry for my english)

Gruß Robert

-- 
sig