0

I am working Microsoft SQL Server Management Studio 2008. I have a table with a column named PAN that has a unique constraint. I looking for a way to auto increment the largest value stored in PAN by 1 if the constraint is violated by trying to insert a duplicate record in the column.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Fundraiser
  • 11
  • 1
  • 3
    Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Oct 02 '18 at 14:20
  • 3
    Most like it you need a `BEFORE INSERT` trigger. But again not sure about what/why are you trying to do – Juan Carlos Oropeza Oct 02 '18 at 14:21
  • 1
    SQL Server supports auto-increment using the identity property. Anything self-written is very likely to fail in a multi-user / multi-threaded environment. – Zohar Peled Oct 02 '18 at 14:21
  • 2
    To me it looks like a [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – Cid Oct 02 '18 at 14:25
  • Does PAN contain meaningful information? Or is it just a surrogate key? The whole point of a unique constraint is to prevent the action that you seem to be trying to allow. – Ryan B. Oct 02 '18 at 14:25
  • 1
    Rolling your own incrementing number is fraught with issues. Either use an identity or a sequence. – Sean Lange Oct 02 '18 at 14:39
  • there isn't a `BEFORE INSERT` trigger in `SQL Server` – Squirrel Oct 02 '18 at 14:43
  • @SeanLange Sequence was introduced in 2012 version... It's possible (and quite easy) to [mimic](https://stackoverflow.com/a/45319922/3094533) to some extent in 2008, though... – Zohar Peled Oct 02 '18 at 15:07
  • @ZoharPeled that is pretty clever but not without issue. I was able to produce a deadlock pretty easily while testing it for concurrency. – Sean Lange Oct 02 '18 at 15:25
  • 1
    @SeanLange Actually, I've never tested this code myself. It was just a proposed solution to that specific problem (though a better solution would be to replace the DBA that "doesn't believe in identity columns")... – Zohar Peled Oct 04 '18 at 07:10

1 Answers1

0

One way, as one of the comments suggested, is a INSTEAD OF INSERT TRIGGER.

Another way is by putting your INSERT in a TRY..CATCH structure.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52