Synchronization of two tables Auto Incremental PK and used as FK in other

See more:

SQL

SQL-Server

Hi All,

I am working on a billing system and for that purpose i have two tables

1- Bill [Bill_ID (PK Auto Incremental, Bigint), date, amount, ... etc]
2- BillDetails [Bill_ID (FK from bill), Item_ID, quantity, unitprice, ... etc]

When a new bill is created a record is inserted into [Bill] table and the last insrted id is used for [BillDetail] that we know a bill can have multiple items sold/purchased.

Now came to problem that occurs when a [Bill] record is inserted and an error occurred in [BillDetail] insertion DB got inconsistency.

So is there any way to get the new Bill_ID of [Bill] before committing its query.

Thank u for ur time and consideration.