SQL Server Error – Updating Multiple Rows with Triggers

sql servert-sqltriggerupdate

I have a Table Products with a Trigger to create a record on each update made to Products (something like a Log).

Everything works fine if I update one record at a time but if I try to update multiple records (Update Products SET Name = "BLABLABLA" WHERE Price > 10) I get the following error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression. The statement has been terminated."

My Trigger is this one:

CREATE TRIGGER [dbo].[NS_Art_Export_ProdUpdate] ON [dbo].[Produtos] FOR UPDATE
    AS BEGIN

    DECLARE @Key1 NVARCHAR(20), @Key2 NVARCHAR(20)
    DECLARE @Changed BIT

    SET @Key1 = (SELECT Counter FROM INSERTED)
    SET @Key2 = (SELECT Prod_Gen FROM INSERTED)
    SET @Changed = 0

    IF UPDATE(Codigo) SET @Changed = 1
    IF UPDATE(NOME) SET @Changed = 1
    IF UPDATE(Familia) SET @Changed = 1
    IF UPDATE(CodIVA) SET @Changed = 1
    IF UPDATE(qtEmbal) SET @Changed = 1
    IF UPDATE(Volume) SET @Changed = 1
    IF UPDATE(PesoBrt) SET @Changed = 1
    IF UPDATE(PesoLiq) SET @Changed = 1
    IF UPDATE(Status) SET @Changed = 1

    IF @Changed = 1
        INSERT INTO Log_NSArtSoft 
            (Tabela,      Tipo,  Key1,   Key2,   Data_Update, 
             Data_Export, Flag_Update, Data_Export2) 
        VALUES 
            ('Produtos',  'U',   @Key1,  @Key2,  GETDATE(), 
             0, 0, 0)

END
GO

Can someone help me with this?
Thanks

Best Answer

The issue is that in SQL Server, Triggers fire once per operation, not once per row. So when you do multi-row operations, the subquery in SET @Key1 = (SELECT Counter FROM INSERTED) is returning a Counter value per each row updated. You need to restructure the INSERT as being a set-based operation as follows:

CREATE TRIGGER [dbo].[NS_Art_Export_ProdUpdate]
ON [dbo].[Produtos]
AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;

  IF (UPDATE(Codigo) OR UPDATE(NOME) OR UPDATE(Familia) OR
      UPDATE(CodIVA) OR UPDATE(qtEmbal) OR UPDATE(Volume) OR
     UPDATE(PesoBrt) OR UPDATE(PesoLiq) OR UPDATE([Status]))
  BEGIN
    INSERT INTO dbo.Log_NSArtSoft
            (Tabela, Tipo, Key1, Key2, Data_Update, Data_Export, Flag_Update, Data_Export2)
      SELECT 'Produtos', 'U', ins.[Counter], ins.[Prod_Gen], GETDATE(), 0, 0, 0
      FROM   INSERTED ins;
  END
END;
GO

Please keep in mind that the UPDATE function only indicates whether or not the column is in the SET clause of the UPDATE statement (in terms of an AFTER UPDATE Trigger; the UPDATE function returns true for all columns in the context of an INSERT operation). It does not indicate whether or not the value of that column has changed. Meaning, for the query of UPDATE table SET ColA = ColA, calling UPDATE([ColA]) will return true even though the value is guaranteed to be the same.

Assuming that you do not want to log rows in which no values for the specified columns have changed, you need to JOIN the inserted and deleted tables and test each column between those two pseudo-tables. When testing the columns, you need to wrap NULLable columns in ISNULL(), and when the column is a string type (not XML) then you need to use a binary Collation to make sure that all changes get logged (even if they equate when linguistic rules are applied). Hence, the SELECT statement would change as follows (I have no idea what the datatype of any of the columns are, so the particular usage below for each one is purely for example):

  SELECT 'Produtos', 'U', ins.[Counter], ins.[Prod_Gen], GETDATE(), 0, 0, 0
  FROM   INSERTED ins
  INNER JOIN DELETED del
          ON del.[Key1] = ins.[Key1]
         AND del.[Key2] = ins.[Key2]
  WHERE  ins.Codigo <> del.Codigo -- NOT NULL number column
  OR     ISNULL(ins.[Status], -9999) <> ISNULL(del.[Status], -9999) -- NULLable number column
  OR     ISNULL(ins.PesoBrt, '1/1/1900') <> ISNULL(del.PesoBrt, '1/1/1900') -- NULLable date column
  OR     ins.NOME <> del.NOME COLLATE Latin1_General_100_BIN2 -- NOT NULL string column
  OR     ISNULL(ins.Familia, N'45#$%$%^$59jh35$%^$%~!@~!@#|{}\[]') <>
         ISNULL(del.Familia, N'45#$%$%^$59jh35$%^$%~!@~!@#|{}\[]')
         COLLATE Latin1_General_100_BIN2 -- NULLable string column

P.S. It is also a good idea to include SET NOCOUNT ON; at the beginning of Triggers and Stored Procedures (unless a client application specifically needs that output, which is usually not the case).

Related Question