Re: problem with sequence number using a trigger

MITCHELL CIFUENTES <mitchell.cifuentes@gmail.com> writes:
> I hope you can help with this. I have a BEFORE trigger that run every time
> an event insert o update occurs
> on a table called stock. It is working fine inserting the correct data on
> the table if the conditions are met,
> and inserting nothing if not.
> The problem is when it rejects the insert statement, the sequence number
> stock_id gets incremented
> even though nothing is inserted on the table. What can i do to stop stock_id
> from incrementing??.

Well, what you *really* ought to do is disabuse yourself of the illusion
that the stock_id sequence must be gap-free.  You are not going to be
able to prevent insertion failures from leaving gaps.  (Or at least not
without a whole lot more pain than it's probably worth --- try searching
the archives for "gap-free sequence" if you insist on that.)

What you could do in this particular case is not have the sequence value
come from the column's default expression, but let the trigger fill it
in, after it's decided everything is okay.  That will fix the problem
as you describe it.  What it won't fix is gaps caused by insertions that
fail somewhere past the trigger stage (for example, due to a
unique-index violation, or an error in a later trigger, or running out
of disk space).

            regards, tom lane