Creating a trigger dynamically inside another trigger/stored procedure?

From: Peter Teoh <htmldeveloper_at_gmail.com>
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

Original text of this message