new to SQL, I'm looking for a way to setup a Trigger that will update Table B when a new row is inserted on Table A. For basic understanding here are some of the columns in my tables:
TABLE A:
+--------------+--------------+
|order_id (PK) |order_details |
+--------------+--------------+
Table B:
+-------------+--------------+-------------+
|item_id (PK) |item_details |order_id (FK)|
+-------------+--------------+-------------+
I'm using a stored procedure like this one to insert data into Table A. Obviously this procedure doesn't work but I wanted to show where my problem starts and maybe there is a better way besides a Trigger. The problem has been getting the order_id
to use within the second INSERT.
/*Parameters for the first Insert Statement*/
@customer_id INT,
@track_num NVARCHAR(50),
@pckg_num NUMERIC,
/*Parameters for the second Insert Statement*/
@manuf_name NVARCHAR(50),
@model_name NVARCHAR(50),
@qty NUMERIC,
@notes NVARCHAR(MAX)
/*Parameter I need for the second statement that doesn't exist until the first statement is committed*/
@order_id INT
AS
BEGIN
/*First Insert*/
INSERT INTO Orders
(customer_id, track_num, pckg_num)
VALUES
(@customer_id, @track_num, @pckg_num)
/*Second Insert*/
INSERT INTO Items
(manuf, model, qty, notes, order_id)
VALUES
(@manuf_name, @model_name, @qty, @notes, @order_id)
END
I'd like to fire the trigger within the same procedure that would then update Table B with item details passed to the procedure from my web app and including the PK (order_id) from the newly created row in Table A as FK in Table B.
I've tried doing this other ways with no luck, came across Triggers and thought it seemed like a good choice. If you have other suggestions about different methods, please feel free! :) Thanks in advance!