2

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!

3
  • You don’t need a trigger. Oct 6, 2019 at 16:45
  • Care to comment further on what I might need then? As I mentioned in the question I'm not certain a Trigger is the best method but I'm struggling to find what I need. Thanks for your help. Oct 6, 2019 at 17:29
  • I’m not sure on the exact syntax, but you can get the order_id returned from the first insert and use it in the second. Oct 6, 2019 at 19:40

2 Answers 2

1

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;
2
  • So it works using your first suggestion, however, it's only putting in the first row of my gridview data. I am using foreach (GridViewRow g1 in GridView1.Rows) and then calling my stored procedure. Is this incorrect? Oct 8, 2019 at 9:38
  • That's... a rather different question. The only thing the stored procedure itself knows about are the parameters passed to it (which are scalar not table-valued), and the database table it is inserting into - it knows nothing of the grid control, or even the data access layer (ADO.NET?). Have you confirmed what arguments are being passed to the SP from the application? Just trapping the call into the DB should allow you to confirm that. Oct 8, 2019 at 20:18
-1

"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.

1
  • Thank you for the clarification and I did understand that but was curious how to write the trigger code? This is what I've found in research but I dont understand how this will "fire" if its not called from somewhere. CREATE TRIGGER ItemTrig ON Items FOR INSERT AS BEGIN INSERT Items VALUES ('') END I'm going to review the article you linked and see if it can help me further. Thanks for your post! Oct 6, 2019 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.