Assign a Resetting Per-Parent Incrementing Column to Each Child Record

Question

I have two tables, a SalesOrderHeader that has an identity primary key TxnID column and a TxnNumber column that I obtain from external software. For example sake I will omit any other columns and lets say the values in the first 3 rows are like so:

TxnID	TxnNumber
1	00001
2	00002
3	00003
...	...

Then I have a SalesOrderDetail table where TxnNumber is a foreign key. I would like for TxnDetailID to behave as follows:

TxnDetailID	TxnNumber
1	00001
2	00001
1	00002
...	...

Basically kind of act as an identity but on the TxnNumber basis, resetting back to 1 once TxnNumber changes. Is there a way to do so in SQL Server so that it will be continuously follow this rule on data insertion? Because I would like to make a composite primary key that is defined something like: TxnNumber-TxnDetailID

Answer 1

Is there a way to do so in SQL Server so that it will be continuously follow this rule on data insertion?

No. Just use an IDENTITY column or SEQUENCE. They only ways to do this create needless complexity and impair concurrency.

And if you need it for display you can use ROW_NUMBER over (order by TxnDetailID), or similar.