在 SQL Server 中插入或删除的输出 [英] Output Inserted or deleted in SQL Server

问题描述

我有一个 SalesTransaction 和一个 Invoice 表:

I have a SalesTransaction and an Invoice table:

Create Table SalesTransaction
(
    SalesTransactionId int Identity(1,1) Primary Key, 
    CustomerId int Foreign key references Customer(CustomerId) ,
    ProductId int  Foreign key references Product(ProductID),
    Price money, 
    Quantity int, 
    Total money, 
    InvoiceId int
)

和

Create Table Invoice
(
     InvoiceId int  Primary Key, 
     InvoiceAmount money Not Null ,
     BalanceAmount money,
     AmountPaid money Not Null
)

我想要的是 Invoice 表中的 Invoiceid 标记 SalesTransaction 表的 InvoiceId.

What I want is the Invoiceid from Invoice table to tag InvoiceId of SalesTransaction table.

首先SalesTransaction的InvoiceId为NULL，添加invoice表s的数据后，应该标记回InvoiceId的InvoiceIdSalesTransaction 表

At first the InvoiceId of SalesTransaction is NULL, and after the data for invoice table s added, it should tag back to InvoiceId of SalesTransaction table

推荐答案

第一个解决方案(只有一个新的外键)

对于以下架构

Create Table Invoice
(
     InvoiceId int Primary Key, 
     InvoiceAmount money Not Null ,
     BalanceAmount money,
     AmountPaid money Not Null
)

Create Table SalesTransaction
(
    SalesTransactionId int Identity(1,1) Primary Key, 
    CustomerId int Foreign key references Customer(CustomerId) ,
    ProductId int  Foreign key references Product(ProductID),
    Price money, 
    Quantity int, 
    Total money, 
    InvoiceId int Foreign key references Invoice(InvoiceId)
)

这是您的 INSERT/UPDATE 查询

Here are your INSERT/UPDATE queries

DECLARE @SalesTransactionId as int
DECLARE @InvoiceId as int

INSERT INTO [dbo].[SalesTransaction]
           ([CustomerId]
           ,[ProductId]
           ,[Price]
           ,[Quantity]
           ,[Total]
           ,[InvoiceId])
     VALUES
           (1
           ,1
           ,1
           ,1
           ,1
           ,NULL)

SET @SalesTransactionId = SCOPE_IDENTITY()
SET @InvoiceId = 1

INSERT INTO [dbo].[Invoice]
           ([InvoiceId]
           ,[InvoiceAmount]
           ,[BalanceAmount]
           ,[AmountPaid])
     VALUES
           (@InvoiceId
           ,1
           ,1
           ,1)

UPDATE [dbo].[SalesTransaction]
   SET [InvoiceId] = @InvoiceId
 WHERE SalesTransactionId = @SalesTransactionId
GO

我还建议您将 Invoice 表的 InvoiceId 列设置为 Identity.

I would also suggest you to set the column InvoiceId of table Invoice as an Identity.

对于以下架构

Create Table Invoice
(
     InvoiceId int Identity(1,1) Primary Key, 
     InvoiceAmount money Not Null ,
     BalanceAmount money,
     AmountPaid money Not Null
)

Create Table SalesTransaction
(
    SalesTransactionId int Identity(1,1) Primary Key, 
    CustomerId int Foreign key references Customer(CustomerId) ,
    ProductId int  Foreign key references Product(ProductID),
    Price money, 
    Quantity int, 
    Total money, 
    InvoiceId int Foreign key references Invoice(InvoiceId)
)

这是您的 INSERT/UPDATE 查询

Here are your INSERT/UPDATE queries

DECLARE @SalesTransactionId as int
DECLARE @InvoiceId as int

INSERT INTO [dbo].[SalesTransaction]
           ([CustomerId]
           ,[ProductId]
           ,[Price]
           ,[Quantity]
           ,[Total]
           ,[InvoiceId])
     VALUES
           (1
           ,1
           ,1
           ,1
           ,1
           ,NULL)

SET @SalesTransactionId = SCOPE_IDENTITY()

INSERT INTO [dbo].[Invoice]
           ([InvoiceAmount]
           ,[BalanceAmount]
           ,[AmountPaid])
     VALUES
           (1
           ,1
           ,1)

SET @InvoiceId = SCOPE_IDENTITY()

UPDATE [dbo].[SalesTransaction]
   SET [InvoiceId] = @InvoiceId
 WHERE SalesTransactionId = @SalesTransactionId
GO

这篇关于在 SQL Server 中插入或删除的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！