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