Trigger not working when inserting multiple records

Question

I have the following trigger working correctly when I insert one record on table Pedidos.

However, when I insert multiple records I get a 512 error message. I've searched around for details about inserting multiple records and triggers, but not found an answer to my problem.

The trigger reads the inserted records and finds values from other tables to modify the value of the column situacion in table planificaciones.

Am I totally wrong in the way I'm trying to do this? Is there any obvious problems in my trigger?

CREATE TRIGGER TRG_INS_PL_SYNC_STATUS_PLA ON dbo.pedidos after insert as begin if @@ROWCOUNT = 0
    return
set nocount on
declare @v_idpla int,
        @v_situacion nvarchar(12),
        @v_nombre nvarchar(50),
        @v_almacen nvarchar(50), 
        @v_status_pedido nvarchar(4);   

set @v_almacen = (select  almacen_pedido from inserted);
set @v_nombre =(select cliente from inserted);
set @v_status_pedido = (select status_pedido from inserted); 
set @v_situacion = (select top 1 nombre from dbo.StatusPlanificacion 
                    where STATUS_PEDIDO = @v_status_pedido);
set @v_idpla = (select top 1 id from dbo.Planificaciones 
                where dia_entrega >= GETDATE() and situacion <> 'Departed'  
                      and nombre like '%'+@v_almacen +'%'+ @v_nombre);
if(@v_idpla is not null)
    begin
        --select Timespan=SYSDATETIME() from inserted;
        select @@rowcount; 
        UPDATE DBO.Planificaciones
        SET situacion = @v_situacion
        WHERE id = @v_idpla;
    end
end

UPDATE & SOLVED: Looking on tanner suggestion i do the next update on code and works, but i think some one can find this more clear and useful. In suggested by tanner, says cursor not best way to do this and the best option is a Join. In my case this insert never goes more than 50 inserts at same time.

    CREATE TRIGGER TRG_INS_PL_SYNC_STATUS_PLA
    ON dbo.pedidos
    after insert as
    begin

declare @v_idpla int,@v_situacion nvarchar(12),@v_nombre nvarchar(50),@v_almacen nvarchar(50), @v_status_pedido nvarchar(4) 
DECLARE c_cursor CURSOR FAST_FORWARD FOR SELECT ALMACEN_PEDIDO, CLIENTE, STATUS_PEDIDO FROM INSERTED;
OPEN c_cursor
fetch next from c_cursor into @v_almacen,@v_nombre,@v_status_pedido
--declared and open cursor chargin values to variables
while @@fetch_status = 0
begin
    -- set values to variables from anothers tables
    set @v_situacion = (select top 1 nombre from dbo.StatusPlanificacion where STATUS_PEDIDO = @v_status_pedido);
    set @v_idpla = (select top 1 id from dbo.Planificaciones where dia_entrega >= GETDATE() and
        situacion <> 'Departed' and nombre like '%'+@v_almacen +'%'+ @v_nombre);
    --check value not null for assigned variable and do update to the value
    if(@v_idpla is not null)
        begin
            UPDATE DBO.Planificaciones
            SET situacion = @v_situacion
            WHERE id = @v_idpla;
        end 
       --move to the next row of cursor
       fetch next from c_cursor into @v_almacen,@v_nombre,@v_status_pedido
end
CLOSE c_cursor
DEALLOCATE c_cursor
end

Your code needs to be re-factored as it incorrectly assumes `inserted` will only contain one record. This will not be the case when multiple records are inserted in one batch. — Alex, Apr 28 '15 at 08:38
possible duplicate of [SQL Server A trigger to work on multiple row inserts](http://stackoverflow.com/questions/2178889/sql-server-a-trigger-to-work-on-multiple-row-inserts) — Tanner, Apr 28 '15 at 08:48
Thanks for the reply Jaco, can you suggest me a sample of multiple recods inserted in one batch working with a trigger consulting another table to find and apply values to the update statement? — user1353547, Apr 28 '15 at 08:51
"In my case this insert never goes more than 50 inserts at same time" – right, but how often and by how many users concurrently? Seriously, you should really try and learn how to solve problems of this kind in a set-based way rather than in a row-by-row manner. I can't vouch for correctness of @mxix's suggestion but that is an example of how a trigger should accomplish its job: in as few steps as possible. — Andriy M, Apr 29 '15 at 12:29

mxix · Answer 1 · 2015-05-13T17:56:50.490

Not sure if the code is 100% correct but should give you an idea..

inserted is a dataset with all rows of that batch. You just need to think as set based operation.

CREATE TRIGGER TRG_INS_PL_SYNC_STATUS_PLA
ON dbo.pedidos
    AFTER INSERT
AS
BEGIN
    UPDATE p
        SET 
            situacion =  i.nombre
    FROM DBO.Planificaciones p
    INNER JOIN (
        SELECT
            v_idpla.id
            v_situacion.nombre
        FROM INSERTED I
        CROSS APPLY (
            select top 1 
                SP.nombre 
            from dbo.StatusPlanificacion SP
            where 
                SP.STATUS_PEDIDO = I.STATUS_PEDIDO
        ) v_situacion
        CROSS APPLY (
            select top 1 
                Pla.id 
            from dbo.Planificaciones Pla
            where 
                Pla.dia_entrega >= GETDATE() and
                Pla.situacion <> 'Departed' and 
                Pla.nombre like '%'+I.ALMACEN_PEDIDO +'%'+ I.CLIENTE
        ) v_idpla
    ) I ON
        P.id = I.id
END

Mxix Thanks for your time, it work's perfectly. Only change the "SET situacion = v_situacion.nombre" for " SET situacion = i.nombre". Tested and Working!!! — user1353547, May 13 '15 at 15:46

Trigger not working when inserting multiple records

1 Answers1

Linked