SQL Server 2016 Trigger issue

Question

I need a little help with a trigger. Where I'm in wrong:

CREATE TRIGGER [dbo].[tr_ins_MyTriggerName]
ON [dbo].MyTableName
FOR INSERT
AS 
    DECLARE 
    @Number nvarchar(20) ,
    @Date datetime ,
    @Supplier nvarchar(12);

    SET @Number = (SELECT Number FROM INSERTED);
    SET @Date = (SELECT Date FROM INSERTED);
    SET @Supplier = (SELECT Supplier FROM INSERTED);
BEGIN
    IF EXISTS (
        SELECT TOP 1 1 FROM MyTableName x
        WHERE x.Number = @Number
            AND x.Date = @Date
            AND x.Supplier = @Supplier
        )
    BEGIN
        RAISERROR ('There is the same Number for the same Supplier!' ,11,1)
        ROLLBACK
    END
END

This trigger runs in all cases even the the EXITS is not true!

Answer 1

I'd suggest you to create a unique index on the 3 columns, i.e. date / number / supplier.

If this is not applicable, I'd say you modify your trigger as follows

CREATE TRIGGER [dbo].[tr_ins_MyTriggerName]
ON [dbo].MyTableName
FOR INSERT
AS 
    DECLARE 
    @Number nvarchar(20) ,
    @Date datetime ,
    @Supplier nvarchar(12);

    SET @Number = (SELECT Number FROM INSERTED);
    SET @Date = (SELECT Date FROM INSERTED);
    SET @Supplier = (SELECT Supplier FROM INSERTED);
BEGIN
    IF  (
        SELECT count(*) FROM MyTableName x
        WHERE x.Number = @Number
            AND x.Date = @Date
            AND x.Supplier = @Supplier
        ) > 1
    BEGIN
        RAISERROR ('There is the same Number for the same Supplier!' ,11,1)
        ROLLBACK
    END
END

However, this trigger is problematic when you insert multiple values in one insert statement.

Answer 2

This trigger runs in all cases even the the EXITS is not true!

The condition will always be true since this trigger fires after the record is inserted. FOR is synonymous with AFTER for TRIGGERS, thus the EXISTS would never not be true.

FOR | AFTER

Specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

AFTER is the default when FOR is the only keyword specified.

When you say it runs do you mean the trigger fires, or that the error is always raised? I'd expect both, as explained above.

It seems like you are trying to do error handling for an INSERT in which I'd do it in the procedure or code block that you are doing the INSERT via a TRY / CATCH block, or just add a constraint on the table to prevent this type of duplicate insertion. Erland Sommarskog has an extensive blog on error handling that's very helpful. Here's a basic framework via one of this posts, but be sure to read it!

Here is a stored procedure that showcases how you should work with errors and transactions.

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH