Re: Creating a trigger dynamically inside another trigger/stored procedure?
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