Oracle trigger causing infinite loop

Question

I have a students table in my Oracle database which has a field called RECORD_NUMBER. This field is 8 characters long and I want to create a trigger to pad the left part out with 0s as it's inserted. This is what I have so far:

create or replace
TRIGGER STUDENTS_RECORD_NUMBER_TRG 
BEFORE INSERT OR UPDATE OF RECORD_NUMBER ON TBL_STUDENTS 
FOR EACH ROW
BEGIN
  WHILE length(:new.RECORD_NUMBER) < 9
    LOOP
      :new.RECORD_NUMBER := LPAD(:new.RECORD_NUMBER,8,'0');
    END LOOP;
  NULL;
END;

However, when I try to insert a row the database connection locks up and I have to restart Oracle to use it again. Is it possible that this trigger is causing an infinite loop?

Justin Cave · Accepted Answer · 2012-12-03 15:26:12Z

2

If record_number is a varchar2(8), then length(:new.record_number) will always be less than 9 and your loop will iterate endlessly. But you don't need a loop here, just call LPAD

create or replace TRIGGER STUDENTS_RECORD_NUMBER_TRG 
  BEFORE INSERT OR UPDATE OF RECORD_NUMBER 
  ON TBL_STUDENTS 
  FOR EACH ROW
BEGIN
  :new.RECORD_NUMBER := LPAD(:new.RECORD_NUMBER,8,'0');
END;

This assumes, of course, that it really makes sense to pad the data that is physically stored in the database rather than doing something like applying the LPAD in a view layer. Generally, I would expect that you'd be better served putting this sort of presentation logic in a view since views are great for implementing presentation logic. But this trigger should do what you've asked.

answered Dec 3, 2012 at 15:26

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

2 Comments

James Dawson Over a year ago

Doesn't seem to work, it doesn't throw any errors however when a row is inserted the trigger fails to pad the record number.

Justin Cave Over a year ago

@JamesDawson - What is the data type of the record_number column? Is it a VARCHAR2(8) or a NUMBER?

David Aldridge · Accepted Answer · 2012-12-03 15:32:26Z

0

Leave the entry as a number. If it needs to be displayed with padded zeroes then do so as part of the display logic.

answered Dec 3, 2012 at 15:32

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Collectives™ on Stack Overflow

Oracle trigger causing infinite loop

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related