CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-trigger
Maciek Trystuła
Maciek Trystuła

Reputation: 59

SQL Server trigger with loop for multiple row insertion

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?

Upvotes: 5

Views: 16639

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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.

Upvotes: 6

TahaAShalan
TahaAShalan

Reputation: 1

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

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

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

Upvotes: 0

Related Questions