Recording Multiple Rows of Data from Table Insertion Using Trigger

Question

I tried creating a trigger to store the data manipulations done on my Food table inside another table called FoodTriggerTable. When I insert only one row of data, I can record the data manipulation well in FoodTriggerTable. However, when I insert multiple rows at once, I only store the information of the first row of data. I have the following trigger:

CREATE TRIGGER InsertFoodTrigger ON Food
AFTER INSERT
AS
    DECLARE @FoodID varchar(5);
    DECLARE @FoodName nvarchar(50);
    DECLARE @FoodDesc nvarchar(200);
    DECLARE @FoodPrice money;
    DECLARE @InsertAction varchar(200);
    DECLARE @InsertActionTime datetime;
    DECLARE @Amount int;

    SELECT @FoodID = i.FoodID FROM INSERTED i;
    SELECT @FoodName = i.FoodName FROM INSERTED i;
    SELECT @FoodDesc = i.FoodDesc FROM INSERTED i;
    SELECT @FoodPrice = i.FoodPrice FROM INSERTED i;
    SELECT @Amount = COUNT(*) FROM INSERTED i;

    SET @InsertAction = 'You''ve inserted ' +  CONVERT(varchar(10), @Amount) + ' data into ''Food'' table';

    INSERT INTO FoodTriggerTable (FoodID, FoodName, FoodDesc, FoodPrice, InsertAction, InsertActionTime)
    VALUES (@FoodID, @FoodName, @FoodDesc, @FoodPrice, @InsertAction, GETDATE());
GO

And I tried the following INSERT:

INSERT INTO Food
VALUES  ('CH006', 'Dummy Data 1', 'Dummy data', '0', '34'),
        ('CH007', 'Dummy Data 2', 'Dummy data', '0', '75');

How do I fix my trigger so that it also records the second row of data manipulation?

Dale K · Accepted Answer · 2020-06-01 23:02:22Z

You need to think set based - inserted is a table, with a row for each record inserted or updated. Just use it as you would a normal table - don't try and convert it to procedural programming e.g.

INSERT INTO FoodTriggerTable (FoodID, FoodName, FoodDesc, FoodPrice, InsertAction, InsertActionTime)
  select FoodId, FoodName, FoodDesc, FoodPrice, null, getdate()
  from Inserted

Note: Not sure what InsertAction you want when its 1 per row now - up to you.

Edit: Procedural is actually the correct term. When doing stuff in T-SQL you are generally manipulating sets of data, which is whats its designed to do, given its a database programming language. So generally you try to frame and solve the problem in terms of sets of data. This is quite apparent when writing a trigger because and insert/update/delete could involve any number of records and you have to handle that. You can of course use cursors and loops to drop back to procedural programming but performance is going to suffer. There are many articles about this out there, here is an example https://www.sqlshack.com/introduction-set-based-vs-procedural-programming-approaches-t-sql/

Max Szczurek · Accepted Answer · 2018-10-31 05:52:03Z

1

CREATE TRIGGER InsertFoodTrigger ON Food
AFTER INSERT
AS
    INSERT INTO FoodTriggerTable (FoodID, FoodName, FoodDesc, FoodPrice, InsertAction, InsertActionTime)
    SELECT FoodID, FoodName, FoodDesc, FoodPrice, 'You''ve inserted ' +  CONVERT(varchar(10), (SELECT COUNT(*) FROM inserted)) + ' data into ''Food'' table', GETDATE()
    FROM inserted
GO

answered Oct 31, 2018 at 5:52

Max Szczurek

4,3342 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Recording Multiple Rows of Data from Table Insertion Using Trigger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related