Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How do you get Triggers and Constraints to "get along"?
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