Re: trigger on alter table to re-create archiving trigger. (advanced) (RP)

On 26 Aug 2002 08:15:45 -0700, jochen.vandenbossche_at_proximus.net (Jochen Van den Bossche) wrote:

>I have a database with lots of tables (313), some tables have many
>columns (332). and, yes, this if fully normalised...
>
>I recently developped an archiving system with a shadow table for
>every table where triggers record any DML.
>
>Fortunatly I first developped a procedure that generates the SQL
>necessary to create the archive tables and the triggers. A problem was
>that to generate the create table and the trigger for this 300+ column
>table, I had to use a CLOB as destination for the SQL code.
>
>This system worked perfectly to create the tables and trigger, but now
>(as foreseen) the next problem arises: The tables are altered now and
>then...
>This forces us to also alter the shadow tables and the triggers.
>
>Altering the shadow tables is easy: it is just a copy paste of the
>alter table statement for the base table with the addition of a few
>columns specic to the archive (always the same).
>
>More difficult is altering the triggers. Currently the developpers
>need to use the generation procedure, get the new trigger from that
>table and then execute it...
>
>I need something like an "on alter table" trigger to regenerate the
>trigger code. And then "execute immediate" the generated code.
>
>Is something like that possible?
>
>Can I put a trigger on user_tab_columns? I think I'll run in to the
>fact that a normal (non system) trigger can not perform DDL. to quote
>the manual: [quote] DDL statements are not allowed in the body of a
>trigger. Also, no transaction control statements are allowed in a
>trigger. ROLLBACK, COMMIT, and SAVEPOINT cannot be used. For system
>triggers, {CREATE/ALTER/DROP} TABLE statements and ALTER...COMPILE are
>allowed.[/quote]
>
>So then I'll have to use an AFTER ALTER (schema) trigger.
>Does anyone have any examples of this kind of triggers? What values
>can I expect in the associated "Atteribute functions"
>(ora_dict_obj_type, ora_dict_obj_name, ora_is_alter_column,
>ora_is_drop_column, ...)?
>
>Does execute immediate work with CLOBs (or statemnts with more than
>4000 characters) or should I look into dbms_sql? Or can I use something
>like varchar(30000) since it is inside a PL/SQL block?
>
>Any help greatly appreciated.
>jochen.

Anything wrong with running ALTER TABLE by means of a procedure regenerating the trigger? Why do you insist on screwing up Oracle?

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Aug 26 2002 - 16:34:30 CDT