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

Comments embedded.
On Feb 13, 2: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).
>

And you shouldn't be.

> 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
>

What purpose does this mess serve? Why, on earth, are you attempting this exercise in futility? Why would you consider replacing a shutdown trigger every time the database starts a smart idea?

> 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.
>

It shouldn't work. This is a mess, to say the least. The 'logic' escapes me.

> Problem - how to create the trigger inside this trigger?  

You don't. It's that simple.

> 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.....
>

WHY? What exactly is this 'need'? What reasoning, however lame, supports this attempted act?

> Please....say YES.....:-)...

Please ANSWER the questions posed of you. You have yet to do so.

David Fitzjarrell Received on Wed Feb 13 2008 - 08:16:24 CST