Trigger in trigger or trigger before table creation

Question

I need to create trigger inside trigger as follows:

SQL> CREATE OR REPLACE TRIGGER table_account_modify AFTER CREATE ON SCHEMA
  2  DECLARE
  3  NAZWA_TABELI varchar(250);
  4  sq0 VARCHAR2 (30000);
  5
  6
  7  BEGIN
  8  select ora_dict_obj_name into NAZWA_TABELI from DUAL;
  9
 10  sq0:= 'CREATE OR REPLACE TRIGGER new_trigger2; BEFORE INSERT on ACCOUNT FOR EACH ROW; BEGIN INSERT INTO pstepien VALUES (:NEW.DN,:NEW.CONTAINERDN,:NEW.SUPERVISOR,:NEW.OWNERDN,:NEW.SELFDN,:NEW.TARGETCLASS,:NEW.ERACCOUNTCOMPLIANCE,:NEW.ERACCOUNTSTATUS,:NEW.ERLASTACCESSDATE,:NEW.ERPARENT,:NEW.ERSERVICE,:NEW.ERUID); END;';
 11
 12
 13  IF NAZWA_TABELI = 'ACCOUNT' THEN
 14  execute immediate sq0;
 15  END IF;
 16  END table_account_modify;
 17  /


Trigger created.


SQL> create table ACCOUNT (
2    DN VARCHAR2(255),
3    CONTAINERDN VARCHAR2(255),
4    SUPERVISOR VARCHAR2(255),
5    OWNERDN VARCHAR2(255),
6    SELFDN VARCHAR2(255),
7    TARGETCLASS VARCHAR2(255),
8    ERACCOUNTCOMPLIANCE VARCHAR2(255),
9    ERACCOUNTSTATUS VARCHAR2(255),
10    ERLASTACCESSDATE VARCHAR2(255),
11    ERPARENT VARCHAR2(255),
12    ERSERVICE VARCHAR2(255),
13    ERUID VARCHAR2(255)
14  );
create table ACCOUNT (
*
ERROR at line 1:
 ORA-00604: error occurred at recursive SQL level 1
ORA-00911: invalid character
ORA-06512: at line 13


SQL>

After I created the trigger, I created a table to see if it works and I got that error. Could you please advice me what the problem could be and how to proceed here?

Answer 1

There are several problems here.

1. The syntax of your dynamic create trigger statement is invalid.
2. The select from dual and definition of NAZWA_TABELI for ora_dict_obj_name is unnecessary.
3. The outer trigger will fire for every table, though it only needs to for one.
4. The dynamic SQL could be a constant but is not.
5. An early return could be used but is not.
6. The If statement could encompass the sq0 definition, but does not.
7. The insert will break if a new column nullable column is added to pstepien.
8. You are creating a trigger to do what normal code should do.
9. You are creating a trigger inside a trigger.

I recommend you create your table and in the package where you have the statement:

INSERT INTO Account...

Add the other insert right beside it.

INSERT INTO Account...
INSERT INTO Pstepien...