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.

How about a schema level trigger, which traps DDL. There's one problem with that. You can't execute DDL inside a Trigger. Possible solution: Generate the code to modify the arc table and recreate the trigger and put it into a job via DBMS_JOB, to be executed in say 1 minute. So with a bit delay, you get the task done.

Here's an example for trapping DDL:

conn SYSTEM/manager_at_demo

DROP USER marc CASCADE
/
CREATE USER marc
IDENTIFIED BY marc
DEFAULT TABLESPACE ts_data
QUOTA UNLIMITED ON ts_data
/
GRANT CREATE SESSION TO marc
/
GRANT ALTER SESSION TO marc
/
GRANT CREATE TABLE TO marc
/
GRANT CREATE TRIGGER TO marc
/
GRANT CREATE PROCEDURE TO marc
/
conn marc/marc_at_demo
CREATE TABLE t_ddl_log
(obj_name VARCHAR2(30),
 obj_type VARCHAR2(30),
 action VARCHAR2(1),
 zeitpunkt DATE)
/
CREATE OR REPLACE TRIGGER log_create

   AFTER CREATE ON marc.SCHEMA
DECLARE

   l_obj VARCHAR2(30);      
   l_type VARCHAR2(30);      
BEGIN                 

   l_obj := dictionary_obj_name;
   l_type := dictionary_obj_type;
   --
   INSERT INTO t_ddl_log
   VALUES
   (l_obj,
    l_type,
    'C',
    SYSDATE);
END;
/
CREATE OR REPLACE TRIGGER log_drop

   AFTER DROP ON marc.SCHEMA
DECLARE

   l_obj VARCHAR2(30);      
   l_type VARCHAR2(30);      
BEGIN                 

   l_obj := dictionary_obj_name;
   l_type := dictionary_obj_type;
   --
   INSERT INTO t_ddl_log
   VALUES
   (l_obj,
    l_type,
    'D',
    SYSDATE);
END;
/
SELECT * FROM t_ddl_log
/
CREATE OR REPLACE PACKAGE mrc$p_test
AS
l_var VARCHAR2(100);
END mrc$p_test;
/
SELECT * FROM t_ddl_log
/
CREATE OR REPLACE PACKAGE mrc$p_test
AS
l_var VARCHAR2(100);
l_var2 VARCHAR2(100);
END mrc$p_test;
/
SELECT * FROM t_ddl_log
/
CREATE OR REPLACE PACKAGE BODY mrc$p_test AS
BEGIN
   NULL;
END mrc$p_test;
/
SELECT * FROM t_ddl_log
/
DROP PACKAGE mrc$p_test
/
SELECT * FROM t_ddl_log
/

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Wed Aug 28 2002 - 14:51:20 CDT