Re: Creating a trigger inside a trigger

I haven't tested this myself but I notice it says in the *Application Developer's Guide - Fundamentals* (10.2) under Coding Triggers: 

Event: STARTUP
Restrictions: No database operations allowed in the trigger. Return status ignored. 

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1432
I don't see anything odd about the rule being enforced within procedures called from a trigger. Surely that is normal and correct.
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?



--
http://www.freelists.org/webpage/oracle-l

Other related posts: