RE: Creating a trigger inside a trigger
- From: <krish.hariharan@xxxxxxxxxxxx>
- To: <htmldeveloper@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Mar 2008 00:37:54 -0700
Notwithstanding the question "Why", especially if your example is indeed the
reality of what you are trying to accomplish ...
I did not do it in the start up trigger but in a before insert trigger. The
before insert trigger creates a "before update for each row which changes
the sign of the value'
Two things I came across.
1. Since the execution is inside a PL/SQL block you have to grant the right
to create a trigger to the user and not via roles
2. Be especially careful with syntax errors since it made SQL*Plus unstable
with SQL*Plus internal errors
3. This will have to be an autonomous transaction since you are committing
inside a trigger (at least in my case since it was a table trigger).
Therefore, appears doable, but WHY ...
Regards,
-Krish
Krish Hariharan
President/Executive Architect, Quasar Database Technologies, LLC
http://www.linkedin.com/in/quasardb
crtrg.ddl
---------
create table crtrg_t
(
c1 number
)
/
create or replace trigger crtrg_test
before insert
on crtrg_t
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate 'create or replace trigger crtig_u'
|| ' before update'
|| ' on crtrg_t'
|| ' for each row'
|| ' begin'
|| ' :new.c1 := -1 * :new.c1;'
||' end;';
end;
/
show error
SQL> @crtrg.ddl
Table created.
Trigger created.
No errors.
SQL> select trigger_name from user_triggers where table_name = 'CRTRG_T' ;
TRIGGER_NAME
------------------------------
CRTRG_TEST
SQL> insert into crtrg_t values (1) ;
1 row created.
SQL> commit ;
Commit complete.
SQL> select trigger_name from user_triggers where table_name = 'CRTRG_T' ;
TRIGGER_NAME
------------------------------
CRTRG_TEST
CRTIG_U
SQL> select c1 from crtrg_t;
C1
----------
1
SQL> update crtrg_t set c1 = 2 where c1 = 1;
1 row updated.
SQL> commit ;
Commit complete.
SQL> select c1 from crtrg_t ;
C1
----------
-2
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Peter Teoh
Sent: Monday, March 03, 2008 11:35 PM
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:
