SQL Server Multi-row insert trigger

Question

I have tested that trigger but it works only with one insert row.

The trigger fails with multiple-row insert and I didn't find the syntax to fix the trigger.

Any suggestion to fix that?

Thanks to the stackoverflow users that let me notice the problem.

USE [MY_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_update_father]
   ON [dbo].[PROD_IVR_CALL]
   AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @tnumber nvarchar(50), @id_inserted int, @id_prod_file int;

         select 
         @id_inserted = ins.id,
         @tnumber = ins.tnumber ,
         @id_prod_file = pf.ID_PROD_FILE
         from inserted ins
         inner join prod_failure pf on (ins.ID_PROD_FAILURE = pf.ID);    

    update prod_ivr_call
    set id_father = sq.ID
    from
        (select min(pic.id) as ID
        from prod_ivr_call pic
        inner join prod_failure pf on (pic.ID_PROD_FAILURE = pf.ID)
        where pic.tnumber = @tnumber 
        and pic.is_requested = 0
        and pf.ID_PROD_FILE = @id_prod_file
        group by pic.tnumber ) sq

END

Answer 1

Your UPDATE statement is not syntactically correct. You can actually merge the two statements of your trigger using a CTE, and then do the UPDATE on this CTE:

ALTER TRIGGER [dbo].[tr_update_father]
   ON [dbo].[PROD_IVR_CALL]
   AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    ;WITH ToUpdate AS (
        SELECT pic.id_father,
               MIN(pic.id) OVER (PARTITION BY pic.tnumber) AS min_id
        FROM prod_ivr_call pic
        INNER JOIN join prod_failure pf ON pic.ID_PROD_FAILURE = pf.ID
        JOIN inserted ins ON ins.ID_PROD_FAILURE = pf.ID
        WHERE pic.tnumber = ins.tnumber AND pic.is_requested = 0
     )
     UPDATE ToUpdate
     SET id_father = min_id

END