AutoIncrement column based of FK

I have a table

 OrderDetail

 PK                                      FK                            
 OrderDetailID                OrderID           LineNo          
 8238                                7780                  1
 8239                                7780                  2
 8240                                7781                  1

 How do I create the column LineNo based of OrderID to increment starting with one?  So once the FK OrderID changes it starts back to 1 for the next orderid.  The lineno field must autogenerate and needs to be stored.
JDay2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
why do you need to store it if you can easily generate it?
Éric MoreauSenior .Net ConsultantCommented:
another issue you might have if you store that value is that if you remove rows from your table, you will get holes in your LineNo sequence
JDay2Author Commented:
I need to store it because it will be tied with order detail information and the line number makes it unique.

I don't want to use the PK field and the viewing of line no per order seems logical.

I will have it so you cannot remove rows and have an option to cancel as a status.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Éric MoreauSenior .Net ConsultantCommented:
it would be easier if your client application handling the orders would provide that value.
Éric MoreauSenior .Net ConsultantCommented:
JDay2Author Commented:
can I make this a stored procedure to calculate?
Éric MoreauSenior .Net ConsultantCommented:
How do you add rows to your table?
JDay2Author Commented:
by sql business rule for that field in .net c#
Vitor MontalvãoMSSQL Senior EngineerCommented:
The only way I'm seeing to do what you want is with a trigger:
CREATE TRIGGER GenLineNumber
ON dbo.OrderDetail
AFTER INSERT, UPDATE
AS  
	WITH CTE_RowNumber
	AS (
        select OrderDetailID, OrderID, ROW_NUMBER ( )  OVER (PARTITION BY OrderID order by OrderDetailID)  as RowNumber
        from OrderDetail
	)
	UPDATE t
	SET t.Lineno = r.RowNumber
	FROM OrderDetail t
		INNER JOIN inserted i ON t.OrderDetailID = i.OrderDetailID
		INNER JOIN CTE_RowNumber r ON t.OrderDetailID = r.OrderDetailID
GO

Open in new window

And then run a dummy update to get all rows updated:
UPDATE OrderDetail 
SET OrderID = OrderID 

SELECT *
FROM OrderDetail 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
If you are not calling a Stored Proc to do the insert, you cannot have a SP to set the value! That's why the article of sqlteam is suggesting a trigger.

If your .Net code is inserting the data directly, better have the .Net code to set the LineNo value as well.
Vitor MontalvãoMSSQL Senior EngineerCommented:
JDay2, a feedback will be appreciated.
Cheers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.