AutoIncrement column based of FK

JDay2
JDay2 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
why do you need to store it if you can easily generate it?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
it would be easier if your client application handling the orders would provide that value.
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:

Author

Commented:
can I make this a stored procedure to calculate?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
How do you add rows to your table?

Author

Commented:
by sql business rule for that field in .net c#
IT Engineer
Distinguished Expert 2017
Commented:
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

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
JDay2, a feedback will be appreciated.
Cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial