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;

error code is something ORA-00604: error occurred at recursive SQL level 1 ORA-01654: unable to extend index SYS.I_TRIGGERCOL1 by 64 in tablespace SYSTEM — HEEN
– HEEN, Commented Jun 16, 2022 at 15:01
Looks like your SYSTEM tablespace has run out of space then; nothing to do with the trigger really, that's just what you happen to be doing when it needs space it can't find. You might be able to do some clean-up but in the short term you can increase the size. Not really a programming issue though. — Alex Poole
– Alex Poole, Commented Jun 16, 2022 at 15:04

Littlefoot · Accepted Answer · 2022-06-16 15:00:34Z

1

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.

answered Jun 16, 2022 at 15:00

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HEEN Over a year ago

error code is something

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

Littlefoot Over a year ago

Right; just Googled it. Is there any schema-level trigger which does something it shouldn't? Owned by SYS (or SYSTEM)? Check select * from dba_triggers.

HEEN Over a year ago

getting error as ORA-00942: table or view does not exist. Yes I have one trigger on another column. is it due to that

Littlefoot Over a year ago

I'd rather say that user - you're currently connected to - doesn't have privileges to query DBA_TRIGGERS. But never mind that; as you commented below the question, you ran out of space so you'll have to talk to DBA.

Collectives™ on Stack Overflow

Error while creating trigger on table in Oracle

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related