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

Home -> Community -> Usenet -> c.d.o.server -> Re: How do you get Triggers and Constraints to "get along"?

Re: How do you get Triggers and Constraints to "get along"?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/01/13
Message-ID: <32DA6CE1.D67@mf.sigov.mail.si>#1/1

Brent Fegley wrote:
>
> Hi! I am having a dilemma with the firing order of triggers and
> constraints in an Oracle 7.x database, and was hoping someone could
> help. Here's the problem:
>
> Actions performed in a BEFORE INSERT trigger execute without error, but
> the insert itself fails due to violation of an integrity constraint.
> One of the actions performed in the trigger is the update of a SEQUENCE
> number using the pseudo-column "nextval". So the sequence number
> increases in value, but the insert itself fails, and I'm left with
> non-consecutive sequence numbers.
>
> Though updating a sequence within a trigger is a somewhat trivial
> example, the problem it reveals has much broader implications:
>

In fact you don't have any problems with triggers and integrity constraints working together. The problem you are pointing at has nothing to do with trigers and constraints, it is exclusively related to the nature of Oracle sequences. Whenever you "use" a new value from sequence generator, you can never again get it from the generator (unles you use CURRVALUE or sequence is defined with option CYCLE), no matter if you commit or rollback the transaction. In fact you don't realy "update a SEQUENCE" as you said, you only SELECT a new number (e.g. SELECT seq_name.nextval FROM dual) from sequence generator, so ROLLBACK and COMMIT have nothing to do with sequences.

You will get exactly the same effect if you won't use trigger. If you insert reccord manualy with seq.nextval in it and you later manualy rollback the transaction, you will experience the same gap of sequence numbers in your records.

> 1) Why would I want to do anything with or within a trigger If I knew
> that the operation as a whole might not succeed because of an integrity
> constraint violation -- especially when some of the actions that
> comprise the "operation" cannot be rolled back?

You are wrong! If you don't use explicit COMMIT/ROLLBACK in your trigger, the whole transaction will be rolled back if integrity constraint is violated. It is irrelevant if actions before rollback are performed by trigger, procedure, aplication, manually .... Your question could be parphrased in "Why would I begin any transaction if I knew thet operation
might not succed because of integrity constraints" - that is why you define integrity constraints for!

> 2) Assuming I want or need to use a trigger, how can I ensure that the
> actions it performs can be reversed if an integrity constraint fails?

Again, the whole transaction is either rolled back or commited, no matter if you use triggers or not! Note however that ROLLBACK has nothing to do with "SELECT from sequence generator".

> (I am assuming that, by design, Oracle executes triggers before checking
> integrity constraints.)

Wrong! Only BEFORE TRIGGERS are executed before checking integrity constraints. AFTER TRIGGERS are executed after records are actualy inserted/updated/deleted into tables so i.c. checking is executed before they fire.

> - Brent

Regards, Jure

-- 
 ===============================================================
 ! Jurij Modic                            Republic of Slovenia !
 !  tel: +386 61 178 55 14                Ministry of Finance  !
 !  fax: +386 61  21 45 84                Zupanciceva 3        !
 !  e-mail: jurij.modic_at_mf.sigov.mail.si  Ljubljana 1000       !
 ===============================================================
Received on Mon Jan 13 1997 - 00:00:00 CST

Original text of this message

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