Hi Kevin,

Why don't you let SQL Server to do this for you?
Make CurrTransNo column identity.
All that you will need to is to insert a new record and to call @@IDENTITY system function.
Yes, this approach has the disadvantage that the table will grow, but you can delete unused entries at any time.

Regards,