| 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
![]() |
![]() |