Re: Audit Trigger

Am Fri, 26 Oct 2001 03:02:37 GMT schrieb "Spencer" <spencerp_at_swbell.net> in
<hn4C7.1169$GC7.225179812_at_newssvr30.news.prodigy.com>:

Hallo Spencer:

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

I think this is not really a problem, because there will be max. 100 inserts or updates each day but lot of queries with thousands of rows...

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

Ok - I understand.

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

This is great - thank you very much...

>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...
>
>@mytrigger.sql
>show errors

Do I have to replace &trigname to TR_TESTTABLE_LEER manually or do I have to put the values somewhere else?

Thanks for your answer, it helps a lot.

Gruß Robert

-- 
sig