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

Solution

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

Comments

[Mr. Bhosale]

add more details about expected output

[Gagan Sharma]

Why are you using Trigger for this ? as this thing you can do in your insert Stored procedure.

[Pரதீப்]

This could make the non duplicate records also not to insert just because of one duplicate existence in the batch

[Damien_The_Unbeliever]

UNIQUE constraints can apply to multiple columns. There's no need to write a trigger here.

[Rohini Mathur]

Exactly this won't allow to insert non-duplicate, i am trying to display error rows in print statement

[Rohini Mathur]

will it be possible to have where clause in unique constraints

[Caius Jard]

Do you mean that you want all attempts to insert duplicate googleid/ski pair only if fleet is is not 008? Because unique constraints can't give that level of control.. a unique constraint will give error on duplicate googleid/sku regardless of fleetid. Even if fleetid is made part of the constraint, 008 cannot be used as a value to override the constraint

[Rohini Mathur]

@CaiusJard: Yes Exactly