Question :
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!
Answer :
Two main ways, neither involving a trigger (generally it’s better to avoid triggers if you can) –
1) Use an OUTPUT
clause with the first INSERT
statement to pick out the generated identity value:
DECLARE @NewOrder TABLE (order_id INT);
INSERT INTO Orders
(customer_id, track_num, pckg_num)
OUTPUT
inserted.order_id INTO @NewOrder
VALUES
(@customer_id, @track_num, @pckg_num);
INSERT INTO Items
(manuf_name, model_name, qty, notes, order_id)
SELECT
@manuf_name, @model_name, @qty, @notes, order_id
FROM @NewOrder;
2) Rather than declaring Orders.order_id
with the identity attribute, create and use a sequence object for it (requires SQL Server 2012 or later):
CREATE SEQUENCE OrderIDSeq AS INT
START WITH 1;
CREATE TABLE Orders (
order_id INT
PRIMARY KEY
DEFAULT (NEXT VALUE FOR OrderIDSeq),
//... other columns as before
);
GO
CREATE OR ALTER PROCEDURE AddOrder(
@customer_id INT, @track_num NVARCHAR(50), @pckg_num NUMERIC,
@manuf_name NVARCHAR(50), @model_name NVARCHAR(50),
@qty NUMERIC, @notes NVARCHAR(MAX)) AS
BEGIN
DECLARE @order_id INT = NEXT VALUE FOR OrderIDSeq;
INSERT INTO Orders
(order_id, customer_id, track_num, pckg_num)
VALUES
(@order_id, @customer_id, @track_num, @pckg_num);
INSERT INTO Items
(order_id, manuf_name, model_name, qty, notes)
VALUES
(@order_id, @manuf_name, @model_name, @qty, @notes);
END;
“A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.” docs
This is why you do noot need to ‘fire a trigger’. Its an event that happens because of something else, i.e. inserting a record in table A.