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

Brent,

I understand your concern, but I think the only thing that can't be 'rolled back' is the current value of the sequence number. This is probably due to the way sequences are designed - i.e. to provide maximum performance. If you treat the sequence number as just a unique key and don't rely on it to be sequential and a complete series, then this problem won't be a problem. In fact, if you use the cache option of sequences to improve performance, you are guaranteed to lose some sequence numbers if you have a db crash. It is best not to rely on the value of the sequence if you don't absolutely have to.

That said, if you really need to make sure the sequence number is rolled back, use your own sequence number from a one-row, one-column table and update it yourself. Harder to implement, but you will always be able to roll back the change. But you'll basically have to lock that table when you get the next sequence number and you can't unlock it until you commit the entire transaction. This could limit your performance significantly depending on the application design. This is why sequences were invented, to avoid the single-threaded nature of this type of design. And the price you pay for it is the inability to 'roll back' a sequence.nextval.

So if you can design around the 'missing sequence #' you can use the faster sequence. If not, use your own sequence number as described. Either will work, but sequences are a lot easier.

Allen

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:
>
> 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?
>
> 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?
> (I am assuming that, by design, Oracle executes triggers before checking
> integrity constraints.)
>
> Any help or guidance would be greatly appreciated. TIA.
>
> - Brent
 

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.