RE: Creating a trigger inside a trigger
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <htmldeveloper@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Mar 2008 08:52:59 -0500
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
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Peter Teoh
Sent: Tuesday, March 04, 2008 1:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Creating a trigger inside a trigger
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?
--
Regards,
Peter Teoh
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
