CODEWITHSUNDEEP
oracle-databasetriggers
HEEN
HEEN

Reputation: 4721

Error while creating trigger on table in Oracle

I am creating a trigger for inserting incremental ID's in my table. But while creating I am getting below error

An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_TRIGGERCOL1 by 64 in tablespace SYSTEM

Here is my trigger query.

create or replace TRIGGER TGR_IPCOLO_BIL
        BEFORE INSERT ON ipcolo_ipfee_calc_bil
    for each row 
begin  
       IF INSERTING THEN 
          IF :NEW."ID" IS NULL THEN 
     select SEQ_IPCOLO_IPFEE_BIL.nextval into :NEW."ID" from dual; 
  end if; 
 END IF; 
end;

Upvotes: 0

Views: 158

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

That error sounds pretty bad (I never saw it before) ... internal dictionary tables?! What is error code? ORA-xxxxx?

Meanwhile, trigger can be simplified to this:

create or replace trigger trg_ipcolo_bil
  before insert on ipcolo_ipfee_calc_bil
  for each row
begin
  :new.id := nvl(:new.id, seq_ipolo_ipfee_bil.nextval);
end;
/

You don't have to check if inserting; what else could it be, if it fires before insert? Also, you don't need select ... into - use sequence directly. nvl makes sure you won't overwrite id if you provided it.

Also, consider using identity column instead, if your database version supports it.

Upvotes: 1

Related Questions