Resolved: Error while creating trigger on table in Oracle

Question:

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;

Answer:

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.

If you have better answer, please add a comment about this, thank you!

Resolved: Error while creating trigger on table in Oracle

Resolved: How to scroll bottom of div at launch?

Resolved: how to get and read an xml file in a zip file using xml.etree

Resolved: The ‘Access-Control-Allow-Origin’ header contains multiple values ‘, ’, but only one is allowed. cors error not resolving

Leave A Reply