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

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<al7lmu0rdoksrqif21vbu3c0opngbhgf92_at_4ax.com>...
> 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

[english translation below]
Dus een procedure die alter table vervangt? En die dus de normale alter table uitvoert en tegelijk de bijhorende alter trigger?
hmmm... interessant... Al zie ik niet direct hoe ik daar zou moeten aan beginnen. Moet ik dan niet zelf het complete statement gaan parsen en zo? Dat zie ik niet echt zitten :)
Het probleem is dat de ontwikkelaars wel kolommen willen veranderen en toevoegen, maar niet wakker liggen van het archief...

Ondertussen hebben we al een andere manier ontwikkeld eigenlijk. Zodanig dat de trigger niet moet worden aangepast, zelfs als de tabel verandert (hoogstens eens hercompileren)

Tussen haakjes: procedures die code generen werken perfect hoor, en de gegeneerde procedures ook. Dat is wat ik nu juist heel leuk vind aan Oracle.

[translation]
So a procedure that replaces Alter table? One that does the normal alter table and then the alter Trigger that goes with it?
hmmm... interesting... Though I do not see how I shoud go about this. Musn't i parse the complete statement myself in that case? That might be a bit too much work :)
The problem is that the developpers do want to change and add columns but they don't care about the archive...

By this time we developped an other way of archiving. In such a way that the trigger does not have to be adapted, even if the table changes (at most a recompile would be needed).

By the way: procedures that generate code work perfectly and the generated procedures themselves too. That is what I like so much about Oracle.

Jochen. Received on Tue Aug 27 2002 - 03:02:42 CDT