That's what I thought was going to be the answer. I was just hoping I
was making a mistake somehow. It's no big deal but I like things
organized and hate giant holes.
Ok, one more thing for one of the batch jobs. No problem I have a
cleanup routine.
Thanks for the help,
Lee Foster/
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Sunday, February 12, 2006 5:11 PM
To: Foster, Stephen
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Trigger/Sequence headache
On Sun, 12 Feb 2006, Foster, Stephen wrote:
> This is going to be one of those stupid problems of mine. I have an
> insert trigger setup to verify that duplicate or repeating information
> isn't storage in the table. If trigger function finds the information
> as a duplicate it returns a NULL and the information isn't added; that
> works. The problem I'm having is that it is incrementing the sequence
> counter even when the data isn't added. Is this something that I have
> to live with or should I be returning something other than a NULL?
The sequence is going to increment upon getting the value.
However, I think, if instead of using a default, you got the next value
in
the trigger after you determined that it wasn't a duplicate and set the
field, it wouldn't increment for this case. This changes some other
behaviors a little (for example DEFAULT in updates as well), so you'd
need
to see whether it'd be acceptable.
Of course, errors, rollbacks and deletes will still leave holes.