Re: Creating a trigger dynamically inside another trigger/stored procedure?

From: gazzag <gareth_at_jamms.org>
Date: Wed, 13 Feb 2008 02:46:42 -0800 (PST)
Message-ID: <00754414-eb20-42ab-a592-b710fae6d50f@e25g2000prg.googlegroups.com>


On 13 Feb, 08:24, Peter Teoh <htmldevelo..._at_gmail.com> wrote:
> I have not been successful in creating a trigger via the following
> statement (using execute immediate).
>
> CREATE OR REPLACE TRIGGER startup_trigger
> AFTER STARTUP ON DATABASE
> BEGIN
>   begin
>         execute immediate 'CREATE OR REPLACE TRIGGER shutdown_trigger
> BEFORE SHUTDOWN ON DATABASE BEGIN do_nothing_proc(); END;';
>   exception
>         when others then
>                 dbms_output.put_line (sqlcode||sqlerrm);
>   end;
> END;
> /
> show errors
> list
>
> But if locate the execute immediate statement into t stored proc, and
> execute the stored proc directly, then no problem, it succeed.   But
> again if I called the stored proc via the trigger, it is not working,
> with NO ERROR indicated anywhere at all.
>
> Problem - how to create the trigger inside this trigger?   bottomline
> is I need to create the shutdown trigger DYNAMICALLY based on some
> startup condition, which is available from startup
> trigger...........so every restart of the database is different.....
>
> Please....say YES.....:-)...

To echo Sybrand's sentiments: why????

-g Received on Wed Feb 13 2008 - 04:46:42 CST

Original text of this message