Sql-server – Using Triggers in SQL Server

sql serverstored-procedurestrigger

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!

Best 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;
Related Question