Creating a trigger dynamically inside another trigger/stored procedure?
Date: Wed, 13 Feb 2008 00:24:56 -0800 (PST)
Message-ID: <c05808d5-d0e8-4a43-a233-3af68207faa0@v17g2000hsa.googlegroups.com>
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.....:-)... Received on Wed Feb 13 2008 - 02:24:56 CST