Creating a trigger inside another trigger. ORA-00604: error occurred at recursive SQL level 1

I’m trying to create a DDL trigger AFTER CREATE which would make another trigger when called.

So I wrote some test code, here it is:

CREATE OR REPLACE TRIGGER Test_Trigger
AFTER CREATE ON SCHEMA
WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE OR REPLACE TRIGGER People_Trigger
    BEFORE UPDATE OF ID ON People
    BEGIN
      DBMS_OUTPUT.PUT_LINE('This code here doesn''t really matter.');
    END;
  ]';
END;
/

CREATE TABLE People (
   ID        NUMBER(6)    PRIMARY KEY
 , Name      VARCHAR2(31)
 , Parent_ID NUMBER(6)    REFERENCES People (ID)
);

But I get the following error when trying to create table People:

Error report -
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: on  line 2
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

Although if I create that trigger (People_Trigger) outside the Test_Trigger, it works fine.

Answer

I have done the following exercise and found that error is thrown when there are not sufficient privileges(Create trigger).

-- execute from sys user
--
revoke create trigger from <your_user>;


-- execute from your user
--
CREATE OR REPLACE TRIGGER Test_Trigger
AFTER CREATE ON SCHEMA WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE OR REPLACE TRIGGER People_Trigger
    BEFORE UPDATE OF ID ON People
    BEGIN
      DBMS_OUTPUT.PUT_LINE('This code here doesn''t really matter.');
    END;
  ]';
END;
/



-- execute from your user
--
CREATE TABLE People (
   ID        NUMBER(6)    PRIMARY KEY
 , Name      VARCHAR2(31)
 , Parent_ID NUMBER(6)    REFERENCES People (ID)
);

On execution, You will find following error:

Error report -
ORA-04088: error during execution of trigger 'TEJASH.TEST_TRIGGER'
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 2
04088. 00000 -  "error during execution of trigger '%s.%s'"
*Cause:    A runtime error occurred during execution of a trigger.
*Action:   Check the triggers which were involved in the operation.




Now,
-- execute from sys user
--
grant create trigger to <your_user>;


-- execute from your user
--


CREATE TABLE People (
   ID        NUMBER(6)    PRIMARY KEY
 , Name      VARCHAR2(31)
 , Parent_ID NUMBER(6)    REFERENCES People (ID)
); -- successful


SELECT
    TRIGGER_NAME,
    TRIGGER_TYPE,
    TRIGGERING_EVENT,
    TABLE_OWNER
FROM
    USER_TRIGGERS
WHERE
    TRIGGER_NAME = 'PEOPLE_TRIGGER';