-1

I have created a table called Sales_Details_Store1 and a second table called Sales_Store1.

They are tied together using RI with Sale_ID (increment 1,1 of bigint type) being the PK in the Sales_Store1 table and the FK being the Sale_ID (bigint) in the Sales_Details_Store1 table.

When declaring the RI between both tables I had used ON DELETE CASCADE and ON UPDATE CASCADE.

The problem I am struggling with is that I need to have my FK updated with the incremented values from the PK, but be able to at the same time update both tables records.

I have had trouble sorting out a trigger or a transaction to do this. Any suggestions?

Sales_Store1
Sale_ID (PK, icrement(1,1), bigint, not null), 
Employee_ID (bigint, null), 
Customer_ID (bigint, null), 
Sale_Date (datetime, null), 
Taxes (money, null), 
Payment_Type (varchar, null), 
Notes (varchar, null), 
Tax_Rate (decimal, null), 
Tax_Status (int, null) 

Sales_Details_Store1
ID (PK, bigint, not null), 
Sale_ID (FK, bigint, null), 
Product_ID (bigint, null), 
Quantity (int, null), 
Unit_Price (decimal, null), 
Discount (decimal, null),
Date_Allocated (datetime, null),
Inventory_ID (bigint, null)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AriG23
  • 3
  • 2
  • The issue is that I need to insert records at the same time within both tables and make sure that the FK values are cascaded from the PK. – AriG23 Mar 21 '18 at 16:32
  • 1
    Please provide the actual table definitions. Not clear what you want to do. What is RI? – paparazzo Mar 21 '18 at 16:52
  • Referential Integrity for RI. – AriG23 Mar 21 '18 at 16:54
  • Edit the question and format. – paparazzo Mar 21 '18 at 17:04
  • Still not clear what you are tying to do. What is the use case to update a FK to a new PK? – paparazzo Mar 21 '18 at 18:11
  • So every time there is sale that is placed in the Sales_Store1 table there should be the ability to maintain referential integrity between said table and Sales_Details_Store1 by making sure the values match up between both the primary and foreign key constraint. – AriG23 Mar 21 '18 at 18:24
  • The primary key from Sales_Store1 is like an autonumber or it increments by 1 every time a new entry or record is created. This must stay the same in the second table (I would best assume by the use of cascading a primary key entry to the foreign key column...). Because of this I need a way to also enter records simultaneously to ensure the PK and FK constraint continue to properly increment. I am not sure if this makes any more sense? – AriG23 Mar 21 '18 at 18:24
  • I know what an identity is and how it works. You are seriously confused. There should be no FK rows to update with the creation of a new PK. You insert a row get the new PK and then use it for the FK inserts. Cascade is something different. – paparazzo Mar 21 '18 at 18:29
  • What have you tried so far??? Where's your code??? – Eric Mar 21 '18 at 19:15

1 Answers1

0

This doesn't really sound like a cascading problem to me. It sounds more like, you need the identity value from the first table when you insert into the second table.

Inside a transaction, you should insert into Sales_Store1, then get the ID(s) (either by Scope_Identity() or use the OUTPUT statement), and then insert the data into Sales_Details_Store1 providing the primary key you got the prior step .

Example:

CREATE TABLE Sale (SaleID BIGINT IDENTITY(1,1) NOT NULL,SaleDate DATETIME2)
CREATE TABLE SaleDetail (SaleDetailID BIGINT IDENTITY(1,1) NOT NULL,SaleID BIGINT,ItemDesc NVARCHAR(200))
GO


BEGIN TRAN t1;
INSERT INTO Sale(SaleDate) SELECT GETDATE();
DECLARE @SaleID BIGINT=SCOPE_IDENTITY();
INSERT INTO SaleDetail (SaleID,ItemDesc) SELECT @SaleID,'Test'
COMMIT TRAN t1;
--ROLLBACK TRAN t1;
GO

SELECT * FROM Sale
SELECT * FROM SaleDetail
GO
DarkKnight
  • 16
  • 2