MySql 事务 主从表_主从表同时插入以及一般性事务处理，觉得不太理想，不知道大家有没好的方法。...

/*添加采购入库数据

1.插入主表

2.插入从表

3.更新最新进价、库存状态

4.更新导入状态*/ALTERProcAddPurcaseInStoreRoom@PurcaseInStoreRoomNoChar(30),@InDateSmallDatetime,@ProviderIDInt,@ArrivePurchaseInspectIDInt,@ArrivePurchaseInspectNoChar(30),@RemarkVarchar(100),@TotalAmountMoney,@InputEmployeeIDInt,@AddDetailSqlVarchar(500)AsDeclare@newIdint,@Stateint,@tmpPidint,@tmpSidint,@tmpPricemoney,@PnoVarchar(30),@Countint,@tmpstrVarchar(500)Set@State=0BeginTran--插入主表InsertIntotPurcaseInStoreRoom (PurcaseInStoreRoomNo, InDate, ProviderID, ArrivePurchaseInspectID, ArrivePurchaseInspectNo, Remark, IsSettled, TotalAmount, SettledAmount, RealPayAmount, InputEmployeeID, InputDateTime)Values(@PurcaseInStoreRoomNo,@InDate,@ProviderID,@ArrivePurchaseInspectID,@ArrivePurchaseInspectNo,@Remark,0,@TotalAmount,0,0,@InputEmployeeID,GetDate())If@@Error<>0BeginRollbackTranReturn-1EndSet@newId=@@Identity--插入从表Set@tmpstr=Convert(Varchar,@newId)Set@tmpstr=Replace(@AddDetailSql,'-Id',@tmpstr)Exec(@tmpstr)If@@Error<>0BeginRollbackTranReturn-1End--更新商品最新进价以及库存状态DeclareTemp_CursorCursorForSelectProductID, StoreRoomID, Price, BatchNo, AmountFromtPurcaseInStoreRoomDetailWherePurcaseInStoreRoomID=@newIdOpenTemp_Cursor--如果没有任何行则直接退出If@@Cursor_Rows=0BeginCloseTemp_CursorDeallocateTemp_CursorEndWhile@State=0BeginFetchTemp_CursorInto@tmpPid,@tmpSid,@tmpPrice,@Pno,@CountSelect@State=@@Fetch_Status--修改最近进价UpdatetProductSetRecCostPrice=@tmpPriceWhereProductID=@tmpPidAndRecCostPrice!=@tmpPrice--修改库存信息--如果该批号商品存在于数据库中则更新if(Exists(SelectStoreRoomIDFromtStockWhereProductID=@tmpPidAndBatchNo=@Pno))UpdatetStockSetAmount=Amount+@CountWhereProductID=@tmpPidAndBatchNo=@PnoelseInsertIntotStock (StoreRoomID, ProductID, BatchNo, ProviderID,[ExpireDate], Amount, CostPrice, IsStopSale)Values(@tmpSid,@tmpPid,@Pno,@ProviderID,GetDate(),@Count,@tmpPrice,0)If@@Error<>0BeginRollbackTranCloseTemp_CursorDeallocateTemp_CursorReturn-1EndEndCloseTemp_CursorDeallocateTemp_Cursor--修改导入状态UpdatetArrivePurchaseInspectSetIsCite=1WhereArrivePurchaseInspectID=@ArrivePurchaseInspectIDIf@@Error<>0BeginRollbackTranReturn-1EndCommitTranReturn@newId