CODEWITHSUNDEEP
c#sql-server-2005
maxbeaudoin
maxbeaudoin

Reputation: 6976

How to design an auto-increment field while allowing manual user input?

The Client table:

Id (PK), int, not null (IDENTITY)
NoClient, int, not null

The form (wireframe):

enter image description here

How to solve that problem SQL-wise?

EDIT. I'm talking about the NoClient column, not ID.

Upvotes: 0

Views: 212

Answers (2)

Robert McKee
Robert McKee

Reputation: 21477

Strictly interpreting those rules, there is no solution. One of those rules is either not correct, or not precise. You can't solve it with an AFTER trigger, because you can't attempt to insert a blank into a numeric field, nor can you with a BEFORE trigger. You can't use a default either.

Now, if you mean that "when left blank" means "when left null", then you can solve it with a very carefully crafted BEFORE TRIGGER. (Or an AFTER TRIGGER, if you can change the field to a nullable int)

If you mean that "when left blank" means that you don't mention the column in your insert/update, then you might be able to get by with a carefully crafted default, by converting a call to GUID via NewID to a very large number.

As a side note, I would tell the designer to go back and redesign it, because whatever solution you do finally come up with, it is not very scalable, and a PITA to do correctly. You have to basically lock the entire table (from reading and writing), do an entire table/index scan to make sure the value you come up with is UNIQUE. You probably should be using the ID field as the client no, possibly seeding the identity with something not starting with 0.

Upvotes: 4

David Libido
David Libido

Reputation: 479

Execute a query to SET IDENTITY OFF first, insert, your ID, then SET IDENTITY ON again.

Upvotes: 0

Related Questions