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

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. Received on Mon Aug 26 2002 - 10:15:45 CDT