[SQL] Trigger/Sequence headache
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? I have a function to re-sequence the table and reset the sequence counter in another application. Normal for that application because of the constant adding and deleting. So if there isn’t another answer then I can write a version of that for this application and run it weekly/monthly depending on need and use. It just drives me nuts with large holes in the table. Thanks Lee Foster/
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Trigger/Sequence headache
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:[EMAIL PROTECTED] Sent: Sunday, February 12, 2006 5:11 PM To: Foster, Stephen Cc: [email protected] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
