Message from discussion How ro append characetrs to auto-incrementing number
Subject: Re: How ro append characetrs to auto-incrementing number
From: Erland Sommarskog <esq...@sommarskog.se>
References: <6D76A707-E2A2-46C1-8B86-5F06EB01C989@microsoft.com> <OQiHjkWQGHA.5400@TK2MSFTNGP09.phx.gbl> <Xns977F54C51BDBYazorman@127.0.0.1> <EAFF0DFF-F19A-42E0-B33A-3AF31FC9B041@microsoft.com>
Organization: Erland Sommarskog
User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32)
Content-Type: text/plain; charset=us-ascii
Date: Tue, 07 Mar 2006 03:36:55 -0800
pmud (p...@discussions.microsoft.com) writes:
> I am not very sure about transactions. Do I have to write teh code for
> transactions in trigger window?
If you are not sure about transactions, you have a lot to learn!
Understanding transactions is essential for implmenting database solutions
and maintaining integrity of the data.
The code that I showed
SELECT @orderid = coalesce(MAX(orderid), 0) + 1
FROM orders WITH (UPDLOCK, HOLDLOCK)
WHERE company = @company
INSERT orders (company, orderid, ...
VALUES (@company, @orderid, ...
is something you would write in a stored procedure.
Note that the transaction is not only needed to get the order number
right. Presumably there is also an OrderDetails table that you need to
fill. And you don't want the order to be store incompletely in the
database, do you?
Speaking of OrderDetails, that table should have a foreign-key constraint
to the Orders table. How is that going to work out if you have one order
table per company? One OrderDetails per company?
Overall, having one table per company is going to lead an increase of
complexity in development seriously.
> Also, with the approach you mentioned,
> will it be possible to have contiguous order nos for each company?
The above is a necessrity to have contiguos order numbers. If you
requirement is contiguous numbers, you *cannot* use autonumber. The
IDENTITY feature is *designed* to not give contiguous numbers.
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at