RE: Creating a trigger inside a trigger
- From: Kurt Franke <Kurt-Franke@xxxxxx>
- To: htmldeveloper@xxxxxxxxx, ineyman@xxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 04 Mar 2008 15:37:28 +0100
@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 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
Other related posts:
