RE: Creating a trigger inside a trigger

Kurt,

You are right.
I meant "DDL causing COMMIT", and typed "EXEC IMMEDIATE". "CREATE TRIIGER" is DDL.

Sorry for confusion.

Igor  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kurt Franke Sent: Tuesday, March 04, 2008 9:37 AM
To: htmldeveloper_at_gmail.com; Igor Neyman; oracle-l_at_freelists.org Subject: RE: Creating a trigger inside a trigger

@Igor,
you are totally wrong.

"EXECUTE IMMEDIATE" will never do any transaction handling by itself. you must do it explicitly, whether following the dynamic statement or possible inside if the statement is a pl/sql block.

and, of course, you may use an "autonomous transaction" inside a trigger if it is necessary do have transaction handling there

@Peter,
the original problem with the trigger is that the trigger is a database startup trigger.
in those triggers no create statements are allowed, otherwise you will always get
ORA-30511: invalid DDL operation in system triggers

creating a new job in the startup trigger will also always fail.

but it is possible to modify the next run date of a job or setting a broken job to running.
of course those modifications always need a commit to take effect. thus it is necessary to use an "autonomous transaction" in the trigger.

if the needed code for the action in the shutdown trigger which should be created is not known until the startup trigger is executed, the only solution is to write this code into a table and then read and excute it from a job enabled for execution like described.

those handling may open a very big security hole and thus must be very carefully to avoid this

regards

kf

...
> You can't do that.
>
> "EXECUTE IMMEDIATE" causes implicit COMMIT, which is not allowed
> inside the trigger (neither is ROLLBACK).
> No matter how deep it's hiiden in the stored procedure, as long as
> stored procedure is called from the trigger.
>
> Igor
>

...
>
> 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
> 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?
>
> --
> Regards,
> Peter Teoh

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



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