| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: using an AFTER INSERT Trigger with sequences?
A copy of this was sent to Josh Gough <exv_at_randomc.com>
(if that email address didn't require changing)
On Thu, 03 Dec 1998 02:23:51 -0500, you wrote:
>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...
there is no way around this -- once selected the sequence value is used. An AFTER row trigger cannot modify the firing table either.
A sequence is definitely NOT a gap free object. A rollback or any error or even a database shutdown may cause gaps. Sequences are used to generate non-blocking, highly concurrent keys. if you need gap free numbers -- you'll need to do something else.
>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;
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Dec 03 1998 - 21:30:19 CST
 |
 |