How to make autoincrement value for each customer SQL Query

Question

I want to increment InvoiceNumber for every Customer in format "customer/invoicenumber" and make it as a trigger (every time when I add some data, It should add InvoiceNumber.

CustomerID  |   Price         |    InvoiceNumber     |
  1         |       100       |         1/1          |
  1         |       200       |         1/2          |
  1         |       250       |         1/3          |
  2         |       400       |         2/1          |
  2         |       100       |         2/2          |
  3         |       20        |         3/1          |
  4         |       10        |         4/1          |
  5         |       1         |         5/1          |

Answer 1

During inserting customer information , you need to insert column 'InvoiceNumber' as null. Then below trigger will update Invoicenumber with new number like 1/2 ...etc

CREATE TRIGGER trig1 ON triggertest
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
    DECLARE @CustomerID INT;
    DECLARE @MaxID INT;
    SELECT @CustomerID = INSERTED.CustomerID FROM INSERTED; 
    SELECT @MaxID=(select ISNULL(rn,0) from
    (select top 1 CustomerID, ROW_NUMBER() OVER(order by CustomerID) as rn from [triggertest] where CustomerID=@CustomerID
    order by rn desc) t)
    UPDATE triggertest SET InvoiceNumber=CAST(@CustomerID as nvarchar)+'/'+CAST(@MaxID as nvarchar) WHERE CustomerID=@CustomerID and InvoiceNumber is null;
END