Use trigger to number each order line

[antonycat]

I am wanting to set up a trigger on our SQL server 2000 to create a sequential order line number in the OrderLines table for OrderID.

I will be inserting data from an OrderLinesAdd table into the OrderLines table but want the LineNumber field to be incremented by 1 for each group of orders grouped by OrderID that I insert into the table eg.

OrderID LineNumber
1 - 1
1 - 2
1 - 3
2 - 1
2 - 2
3 - 1
3 - 2

I have just started using SQL so am not sure how I go about writing the code for the trigger ? It would seem like I will need to use the Max function in the code.

Thanking you in advance.

 

[jbenson001]

Look up Create Trigger in books on line. Yes you are correct, you will need to do a Max(LineNumer) where orderID = @something. Then add 1 for your next sequence number.

Jim

 

[vongrunt]

Same old question : what is the primary key on that table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[antonycat]

I have an OrderLineID as the primary key but I need to use the LineNumber and OrderID fields to generate another reference for 3rd party software.

Anyway this is what I have written so far but it does not work and I am not sure why.

CREATE TRIGGER LineNumbering
ON [dbo].[RoutingOrdersTransactions]
FOR INSERT
AS
DECLARE @LineNumber INTEGER
Select @LineNumber = Max(LineNumber)
FROM RoutingOrdersTransactions
WHERE RoutingOrdersTransactions.OrderID=(SELECT OrderID FROM INSERTED)
UPDATE RoutingOrdersTransactions
SET LineNumber=@LineNumber+1
WHERE RoutingOrdersTransactions.OrderID=(SELECT OrderID FROM INSERTED)

Any help would be greatly appreciated.

Ant