Output Inserted or deleted in SQL Server

问题:

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
)

and

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

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

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

回答1:

First solution (with only a new Foreign Key)

For the following schema

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)
)

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

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

Second solution (with a new foreign key and an identity for invoice pk)

For the following schema

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)
)

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