Event: STARTUPRestrictions: No database operations allowed in the trigger. Return status ignored.
btw "when others then dbms_output.put_line()" is asking for trouble, and while I'm at it what is sqlcode there for, given that sqlerrm already starts with the error code?
-----Original message----- From: Peter Teoh Date: 4/3/08 06:34
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 Problem is how to create the trigger inside a trigger? If I relocate the execute immediate statement into another stored proc, and execute the stored proc directly, then no problem in creating the trigger. But if I called the stored proc via the trigger, it is not working, with NO ERROR indicated anywhere at all. Funny thing is no matter how deeply embedded the stored procedure that create the trigger is (sp A->sp B->sp C->sp D->calling create trigger, and then using trigger A to call sp A), Oracle seemed to remember / recognize the deeply embedded trigger, and refuse to create it inside another trigger. Any new insights?