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

On Feb 13, 9: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.....:-)...

Databases are a conceptual unified whole. They are not a mess.
The database startup trigger doesn't make sense at all, and must be considered a kludge.
Apart from that: database shutdown triggers do not fire on shutdown abort, so their use is limited anyway.
What are you trying to accomplish you need such abysmal code, demonstrating you should be pointed to the door of unemployment ASAP?

--
Sybrand Bakker
Senior Oracle DBA