SQL Server trigger with loop for multiple row insertion

Question

I've created trigger for my database which handles some insertion but when I add multiple values in 1 SQL query it doesn't work:

ALTER TRIGGER [dbo].[ConferenceDayTrigger]
ON [dbo].[Conferences]
AFTER INSERT
AS 
BEGIN
    DECLARE @ID INT
    DECLARE @dayC INT
    DECLARE @counter INT

    SET @counter = 1
    SET @ID = (SELECT  IDConference FROM Inserted)
    SET @dayC = (SELECT   DATEDIFF(DAY, start,finish) FROM Inserted)

    WHILE @counter <= @dayC + 1 
    BEGIN
        EXEC AddConferenceDay @Id, @counter
        SET @counter = @counter +1
    END
END

For single insertion it works ok. But what should I change/add to make it execute for each row of inserted values?

Loops in SQL are almost always a terrible idea. Loops inside triggers are like a bad idea squared. Now maybe you can't get around it and you have to do it this way, but I sincerely hope not! PS You need to loop through the rows in `inserted` table, likely with a cursor (another bad idea!) — DavidG, Jan 31 '17 at 01:04

score 4 · Answer 1 · edited Jan 11 '19 at 07:46

If you cannot change the stored procedure, then this might be one of the (very few) cases when a cursor comes to the rescue. Double loops, in fact:

ALTER TRIGGER [dbo].[ConferenceDayTrigger]
ON [dbo].[Conferences]
AFTER INSERT
AS 
BEGIN
     DECLARE @ID INT;
     DECLARE @dayC INT;
     DECLARE @counter INT
     SET @counter = 1;

     DECLARE yucky_Cursor CURSOR FOR
        SELECT IDConference, DATEDIFF(DAY, start,finish) FROM Inserted; 
     OPEN yucky_Cursor; /*Open cursor for reading*/


     FETCH NEXT FROM yucky_Cursor INTO @ID, @dayC;
     WHILE @@FETCH_STATUS = 0 
     BEGIN
         WHILE @counter <= @dayC + 1
         BEGIN
             EXEC AddConferenceDay @Id, @counter;
             SET @counter = @counter + 1;
         END;
         FETCH NEXT FROM yucky_Cursor INTO @ID, @dayC;
     END;

    CLOSE yucky_Cursor;
    DEALLOCATE yucky_Cursor;
END;

I suspect there is a way to refactor and get rid of the cursor and use set-based operations.

score 0 · Answer 2 · answered Jan 31 '17 at 01:08

When you insert more than one record, you need to cursor/while to call the AddConferenceDay procedure for each record.

But I will suggest you to alter your procedure to accept table type as input parameter. So that more than one ID and dayC as input to AddConferenceDay procedure. It is more efficient than your current approach.

something like this

create type udt_Conferences as table (ID int,dayC int)

Alter the procedure to use udt_Conferences as input parameter

Alter procedure AddConferenceDay (@input udt_Conferences readonly)
as 
begin
/* use @input table type instead of @Id and @counter variables */
end

To call the procedure update the trigger with created udt

ALTER TRIGGER [dbo].[ConferenceDayTrigger]
ON [dbo].[Conferences]
AFTER INSERT
AS 
BEGIN
Declare @input udt_Conferences

insert into @input (ID,dayC)
select IDConference,DATEDIFF(DAY, start,finish)  from Inserted
END

score 0 · Answer 3 · answered Sep 03 '17 at 02:38

0

add these lines to your trigger

AFTER INSERT AS BEGIN

AFTER INSERT
AS
BEGIN

    Declare @Count int;
    Set @Count=@@ROWCOUNT;
    IF @Count=0
    Return;

    SET NOCOUNT ON;

-- Insert statements for trigger here

answered Sep 03 '17 at 02:38

TahaAShalan

1
2

SQL Server trigger with loop for multiple row insertion

3 Answers3