同步两个表自动增量PK，并在其他表中用作FK [英] Synchronization of two tables Auto Incremental PK and used as FK in other

问题描述

大家好，

我正在开发一个计费系统，为此，我有两个表

1- Bill [Bill_ID(PK自动递增，Bigint)，日期，金额等...
2- BillDetails [Bill_ID(票据中的FK)，Item_ID，数量，单价等...

创建新帐单时，将一条记录插入[Bill]表中，并且最后一个插入的ID用于[BillDetail]，我们知道该帐单可以出售/购买多个项目.

现在出现了当插入[Bill]记录并且[BillDetail]插入DB中发生错误时发生错误的问题.

因此，有什么方法可以在提交查询之前获取[Bill]的新Bill_ID.

感谢您的宝贵时间和考虑.

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.

推荐答案

请检查插入顺序.

将记录插入到Bill Table中，并使用@@ IDENTITY获取Billid.

现在，将单个记录插入BillDetails.

Hi,

Please check the order of insertion.

Insert the record into the Bill Table and get the billid using @@IDENTITY.

Now insert the individual record into BillDetails.

这篇关于同步两个表自动增量PK，并在其他表中用作FK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！