Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> using an AFTER INSERT Trigger with sequences?
Hello,
From the help of some previous posts on here, I've implemented the
following code to
create a unique id from a sequence, however, if an insertion fails, the
sequence still gets incremented...
Is there any way to prevent this? The only way I can think of is to use
an AFTER insert instead of a BEFORE to change the value. To do this, I
would always insert 0 as the ID, but keep a BEFORE UPDATE trigger to
prevent the value from being changed. Anyone have any other ideas?
thanks,
Josh Gough
CREATE TRIGGER PERSON$TRG_CREATE_PERSON_ID before insert or update ON
PERSON
FOR EACH ROW
DECLARE iCounter PERSON.ID%TYPE; cannot_change_counter EXCEPTION; BEGIN IF INSERTING THEN SELECT PERSON$PERSON_ID.NEXTVAL INTO iCounter FROM DUAL; :new.ID := iCounter; END IF; IF UPDATING THEN IF NOT (:new.ID = :old.ID) THEN RAISE cannot_change_counter; END IF; END IF; EXCEPTION WHEN cannot_change_counter THEN raise_application_error(-20000, 'Cannot Change $ END NODE$TRG_CREATE_NODE_ID;Received on Thu Dec 03 1998 - 01:23:51 CST
![]() |
![]() |