I have searched a lot to find the easiest way to develop this without using temp table as it has its own disadvantages, but could find an answer.
I have a table that the transactions of all the branches of a company will be saved in there.
Branch FormNumber A 1 B 1 A 2 A 3
I have a formNumber field that must start from 1 and automatically increases by one for each new record that will be saved for each branch.
I wonder how I can automate generating the form number based on the branch.
I tried to use after insert trigger but it cant use the same table to find the last FormNumber of current Branch.
I am using Sql server 2016
Any advice would be appreciated.
Every time you add a new row, you can use this insert query:
INSERT INTO your_table(Branch, FormNumber) SELECT "A", MAX(FormNumber) + 1 FROM your_table where your_table.Branch = "A"
it will increase in one the column FormNumber.