How to perform INSERT/UPDATE to Linking (Join) table which has FKs to IDENTITY PKs

Question

I am new to designing my own database. My project is orders/products/inventory type data, using SQL Server 2005. During my reading I came across several customer/order type of examples, and I decided to use linking-table (sometimes here called junction, or join table) for my Order_Details since I had many-to-many relationships between ORDERS and PRODUCTS.

The problem is, I’m not sure what T-SQL structure or syntax I can use to insert/update this ORDER_DETAILS linking-table. The examples make it seem obvious that you would store order details separately, but I don’t know how to keep it in sync with the Orders and Products tables. I was imagining some kind of View which would have them all joined together, where one update would write to all three tables, but I found that a View is only allowed to update one table at a time. I tried a “workaround” by using an “Instead OF” trigger on the view, but I can’t figure out how to write the INSERT statements in the trigger, since the FK values I must insert to the linking table are the IDENTITY PKs from the parent tables, hence are unknown ahead of time.

Then I thought maybe this would be a case of UPDATE CASCADE on the FKs at the linking table, and just perform my INSERTS there, but still it seems like very confusing. I bought a book, and spent a large amount of time learning to design a normalized database, but now I can't figure out how to insert data into it.

My plan is to later write an VB.NET 2005 application with some DatagridViews, etc. where the user can create orders, update products, etc. I am much better with applications, but this time I thought I would rather learn to design a proper database, and then build Views which could just be connected to application controls, i.e. let SQL do most of the work. Can anyone please shed light on the JOIN/UPDATE/SPROC/VIEW which is necessary to maintain these Linking-tables? Thanks.

To help explain the layout, here are some schema details:

ORDERS
----------------
OrderID  PK
OrderDate
EmployeeID fk
CostCenterID fk
etc.


ORDER_DETAILS
-----------------------
OrderID  CPK/fk
ProductID CPK/fk
Qty
OrderDetailComment


PRODUCTS
---------------------
ProductID PK
PartNumber
ProductName
etc.

Here is the current definition for the linking table:

CREATE TABLE [Order_Details] (
    [OrderID] INTEGER NOT NULL,
    [ProductID] INTEGER NOT NULL,    
    [Qty] INTEGER NOT NULL,
    [OrderDetailComment] VARCHAR(100),
    CONSTRAINT [CPK_Order_Details] PRIMARY KEY ([OrderID],[ProductID]),
    CONSTRAINT [Order_Details_Orders] FOREIGN KEY ([OrderID]) REFERENCES [Orders] ([OrderID]),
    CONSTRAINT [Order_Details_Products] FOREIGN KEY ([ProductID]) REFERENCES [Products] ([ProductID])    
)
GO

Answer 1

What kind of client / database access technology do you plan to use?

If you use something like Linq-to-SQL or the Entity Framework - you won't even have to rack your brain about those questions - all is handled nicely for you.

Basically, the steps have to be:

• insert the order and get the order ID back - number of ways to do that
• then insert all the order details, assuming you have the product id's, and now you also have the order id

To store the Order, you can:

• use a stored procedure which returns the newly created OrderID (use the "OUTPUT" clause on your INSERT statement, or check out SCOPE_IDENTITY())
• create a inline SQL statement that does one of the above things

So, either use something like (as a direct inline SQL, or wrapped up in a stored proc - your choice):

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT Inserted.OrderID
VALUES (........)

or just do

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
VALUES (........)

DECLARE @NewOrderID INT
SET @NewOrderID = SCOPE_IDENTITY()

Does that help at all??

UPDATE:
If you want to use the newly inserted ID in subsequent T-SQL code, your best option is to store them into a in-memory table variable - something like this:

DECLARE @NewOrderIDs TABLE (NewOrderID INT)

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT INSERTED.OrderID INTO @NewOrderIDs 
VALUES  (..........)

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT INSERTED.OrderID INTO @NewOrderIDs 
VALUES  (..........)

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
OUTPUT INSERTED.OrderID INTO @NewOrderIDs 
VALUES  (..........)

SELECT * FROM @NewOrderIDs 

If you want to just insert a single order, gets its new OrderID, and then keep going inserting the order details, using the SCOPE_IDENTITY() might be easier:

INSERT INTO dbo.Orders(OrderDate, EmployeeID, CostCenterID)
VALUES  (..........)

DECLARE @NewOrderID INT
SET @NewOrderID = SCOPE_IDENTITY()

INSERT INTO dbo.OrderDetails(.....)
........