Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Why don't you just run separate create table followed by create trigger statements? What's creating the trigger after being triggered by DDL giving you? –  Chris Saxon Aug 5 '13 at 15:58
add comment

1 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...
share|improve this answer
    
Thank You Leigh! i will try to find out where there is "INSERT INTO Account" and i will add INSERT INTO Pstepien if possible. I will let you know the feedback. Thanks! –  user26747 Aug 6 '13 at 15:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.