[SQL] Trigger/Sequence headache

[Foster, Stephen]

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/