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

On Feb 13, 3:24 am, 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.....:-)...

Hi Peter,

At startup, why can't you store in a table (or just check on the fly, I don't know what your conditions are) the conditions that determine what the behavior on shutdown should be? You could then evaluate and implement the process rather than recreating the trigger every time you open the database.

My guess is Oracle keeps you from doing this for a reason...:)

HTH, Steve Received on Wed Feb 13 2008 - 12:43:31 CST