Bug: Doctrine Oracle ID-Triggers.

February 1, 2010 by Reboot · Leave a Comment 

Doctrine’s YAML created the following trigger to allow the creation of IDs via a sequencer.
Notice that the trigger name is not entirely upper-cased: USER_seq which in our case caused an error of the type: OCI_NO_DATA

CREATE OR REPLACE TRIGGER USER_AI_PK
   BEFORE INSERT
   ON USER
   FOR EACH ROW
DECLARE
   last_Sequence NUMBER;
   last_InsertID NUMBER;
BEGIN
   IF (:NEW.id IS NULL OR :NEW.id = 0) THEN
      SELECT USER_seq.NEXTVAL INTO :NEW.id FROM DUAL;
   ELSE
      SELECT NVL(Last_Number, 0) INTO last_Sequence
        FROM User_Sequences
       WHERE Sequence_Name = 'USER_seq';
      SELECT :NEW.id INTO last_InsertID FROM DUAL;
      WHILE (last_InsertID > last_Sequence) LOOP
         SELECT USER_seq.NEXTVAL INTO last_Sequence FROM DUAL;
      END LOOP;
   END IF;
END;

About Reboot
Software Engineer - http://www.linkedin.com/in/orlissenberg

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!