Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> using an AFTER INSERT Trigger with sequences?

using an AFTER INSERT Trigger with sequences?

From: Josh Gough <exv_at_randomc.com>
Date: Thu, 03 Dec 1998 02:23:51 -0500
Message-ID: <36663C87.4ED380CD@randomc.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US