創建儲存過程以在聯結表和其他表上插入多行

我是數據庫和設計的新手，在 INSERT 問題上需要一些幫助。我想創建一個將多個值插入不同表的過程，我需要確保它保持原子，所以我需要在過程中使用事務。

發票表

–Invoice_ID (PK)

–Purchased_Date

Fruit_Table

–Fruit_ID (PK)

–Fruit_Name

Driver_Table

–Driver_ID (PK)

–Driver_Name

–Driver_LastName

發票明細表

–Invoice_ID FK

–Driver_ID FK

–Fruit_ID FK

• 數量

• 數量

我創建聯結表（Invoice_Details_Table）的原因是某些發票將有多個驅動程序和一個或多個水果。

例如，完成了一次旅行，因此我們需要創建一張發票 (1)。這次旅行有兩個司機，送了草莓和蘋果。

第二次旅行，因此需要創建發票 (2)。這次送貨只有一名司機，而且只送了蘋果。

我遇到的問題是如何創建一個過程來更新發票表和 Invoice_Detail_Table 並在插入過程中發生某些事情時回滾。另外，我如何傳遞驅動程序/水果參數，因為有時它會不止一個。

我正在使用 SQL Server 2012。非常感謝任何幫助

所以你想要的是每個表的插入、更新和刪除過程。然後，您需要一個“包裝器”過程來跟踪插入/更新/刪除並相應地響應錯誤。

“事務”將是批處理，“回滾”將是明確的。我將這些放在引號中是因為您不想將所有交易包含在單個交易中。你不能嵌套使用者事務，一個大的單個事務會導致一些嚴重的阻塞。

插入的虛擬碼…

Exec usp_NewInvoice 
     @purchaseDt=@purchaseDt
   , @fruitID   =@fruitID
   , @driverID  =@driverID
   , @qty       =@qty
   , @price     =@price

--parameters are passed with the variable values created by the app

usp_NewInvoice
     @purchaseDt datetime2
   , @fruitID    int
   , @driverID   int
   , @qty        int
   , @price      money

   TRY
   BEGIN
       @ReturnValue= exec usp_InsertInvoice 
                         @purchaseDt=@purchaseDt
                       , @invoiceID=@invoiceID OUTPUT

       if @ReturnValue<>0 
       BEGIN
           --Error handling and rollback
           goto ERROR
       END
   END

   CATCH
   BEGIN
       @ReturnValue<>0 
       BEGIN
           --Error Handling and rollback
           goto ERROR
   END

   BEGIN
       @ReturnValue= exec usp_InsertInvoiceDetail 
                         @invoiceID =@invoiceID
                       , @fruitID   =@fruitID
                       , @driverID  =@driverID
                       , @qty       =@qty
                       , @price     =@price

       if @ReturnValue<>0 
       BEGIN
           --Error handling and rollback
           goto ERROR
       END
   END

   CATCH
   BEGIN
       @ReturnValue<>0 
       BEGIN
           --Error Handling and rollback
           goto ERROR
   END

ERROR:
IF @ReturnValue <>0
BEGIN
   --RETURN ERROR
END

對於每個操作，您要確保是否成功。如果不是，您將需要使用其他過程（更新和/或刪除）回滾所有先前的事務。您需要對更新和刪除執行類似的操作。

引用自：https://dba.stackexchange.com/questions/138387