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.

Example :

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.

more