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
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
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)