RE: Creating a trigger inside a trigger

From: <krish.hariharan_at_quasardb.com>
Date: Tue, 4 Mar 2008 00:37:54 -0700
Message-ID: <006d01c87dca$a98b3ec0$6501a8c0@BHAIRAVIPC01>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Teoh
Sent: Monday, March 03, 2008 11:35 PM
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l



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

Received on Tue Mar 04 2008 - 01:37:54 CST