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

From: Doug Meredith <dougm_at_nb.sympatico.ca>
Date: 1997/01/14
Message-ID: <32dbc3ce.19506862@doormouse.formalsys.ca>#1/1

Brent Fegley <bdfegley_at_ptdprolog.net> 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.

Triggers are a mechanism for getting unique numbers, not sequential numbers. If you need sequential numbers, sequence is not the tool to use.

>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?

Easy. Never do a commit or rollback in the trigger, and change the way you think about sequences.

---

Doug Meredith
Manager, Consulting Services
Skyridge Systems Inc.
Moncton, NB, Canada

Tel: (506) 854-7997
Fax: (506) 855-9673
doug.meredith_at_skyridge.com Received on Tue Jan 14 1997 - 00:00:00 CST