RE: Creating a trigger inside a trigger
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <Kurt-Franke@xxxxxx>, <htmldeveloper@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Mar 2008 10:04:36 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kurt Franke
Sent: Tuesday, March 04, 2008 9:37 AM
To: htmldeveloper@xxxxxxxxx; Igor Neyman; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
