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