SQL Server trigger with error message

Question

I have written a trigger which block duplicate records if found in two column.

how can I throw the rows in message which is causing duplicate. I am trying to show those error rows in error message.

Code:

CREATE TRIGGER [dbo].[BlockDuplicates]
 ON [dbo].[table]
 INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 FROM inserted AS i 
    INNER JOIN dbo.WSP_INPUT_ACTV AS t
    ON i.GoogleID = t.GoogleID
    AND i.SKU = t.SKU

     WHERE i.FleetID <> 008
  )
  BEGIN
    INSERT dbo.WSP_INPUT_ACTV (COL1,COL2,COL3)
      SELECT COL1,COL2,COL3 FROM inserted;
  END
  ELSE
  BEGIN
    PRINT 'INSERTION ABORTED';
  END
END

Answer 1

Change the logic of your trigger around so it does an EXISTS (or pulls some values from the table joined wih the inserted virtual table if you want to specifically mention some / all bad values in the error message) and if values already exist, calls RAISERROR followed by ROLLBACK TRANSACTION

If no values already exist, finish the trigger and insert / commit your values . Something like this (might need a bit of debugging, on an iPad currently, sorry), though note it only selects one bad pair. If you want your error message to contain all bad pairs, it could be huuuuuge..

CREATE TRIGGER [dbo].[BlockDuplicates]
 ON [dbo].[table]
 INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @dupes VARCHAR(100);

  SELECT @dupes = MAX(CONCAT(I.GoogleID, '/', I.sku)) FROM inserted AS i 
    INNER JOIN dbo.WSP_INPUT_ACTV AS t
    ON i.GoogleID = t.GoogleID
    AND i.SKU = t.SKU

     WHERE i.FleetID <> 008
  )

  IF @dupes IS NULL THEN 
  BEGIN
    INSERT dbo.WSP_INPUT_ACTV (COL1,COL2,COL3)
      SELECT COL1,COL2,COL3 FROM inserted;
    COMMIT TRANSACTION;
  END
  ELSE
  BEGIN
    RAISERROR(CONCAT('googleid/sku pair ',@dupes,' is already present'),-1,-1);
    ROLLBACK TRANSACTION;
  END
END