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;