SQL Server 2016 Trigger issue
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 &gt; 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH