Re: Audit Trigger

"Robert Fischer" <robert_fischer_at_gmx.de> wrote in message news:57agttkj16pu822qnnj452r2co13bq4klk_at_4ax.com...
> 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);
> end;
>
> Do you understand what I mean?
>
> (sorry for my english)
>
> Gruß Robert
>
> --
> sig

[Quoted] what you've got here is a good start on what is actually a bad idea -- opening a cursor to query a dictionary view for every row that is updated or inserted into your table is not going to speed things up.

[Quoted] as a general rule, don't be querying dictionary views every time a row is inserted or updated... just write the trigger and be done with it.

[Quoted] you probably don't want to perform the UPPER function on every column in the table. if you don't even know the names of the columns, are you sure that all of the columns in the table are of datatype character? you don't really want to perform the UPPER function on columns of type DATE, LONG, NUMBER, RAW, etc. not to mention the columns based on user-defined object types...

here's an idea... once you've created the table, you could could use SQL*Plus to quickly generate the assignment statements that are needed in the body of the trigger, something like this:

set pagesize 0
set linesize 800
set trimspool on
set echo off
set feedback off
spool myoutputfile
select ' :new.'||rpad(column_name,31)|| ':= upper(:new.'||rpad(column_name,31)||');'   from user_tab_columns
 where table_name = 'A_TESTTABLE_LEER'
   and data_type IN ('CHAR','VARCHAR2','VARCHAR'); spool off

then simply copy the contents of the output file and paste it (in your favorite pl/sql editor) into the body of the trigger.

[Quoted] if you want to get really fancy, you could write a SQL*Plus script to spool the whole "create trigger" statement for you, something like this:

spool mytrigger.sql
select 'CREATE OR REPLACE TRIGGER '||upper('&trigname')   from dual;
select ' BEFORE INSERT OR UPDATE OF '||upper('&tabname')   from dual;
select ' FOR EACH ROW'
  from dual;
select ' BEGIN'
  from dual;
select ' :NEW.'||rpad(column_name,31)|| ':= UPPER(:NEW.'||rpad(column_name,31)||');'   from user_tab_columns
 where table_name = upper('&tabname')
   and data_type IN ('CHAR','VARCHAR2','VARCHAR')  order by column_id;
select 'END;'
  from dual;
select '/'
  from dual;
spool off

Rem once you get the previous part tested, you could Rem even have the SQL*Plus script execute the Rem generated statement for you...

_at_mytrigger.sql
show errors

HTH Received on Fri Oct 26 2001 - 05:02:37 CEST