After update Trigger firing, when there’s an EXISTS clause to prevent the update

My app is throwing an exception on an “after update” trigger in the database when the following query is run against a record that contains an email address that already exists on another user. In other words, the record hasn’t been updated, but should have been ignored because of the EXISTS clause.

Does anybody know why it might be firing the event? Or should the trigger be testing some flag to ensure that it’s a real update?

I’ve tested my code after disabling the trigger and it does exactly as expected so I’m assuming that my query is correct.

An email address can be blank, but otherwise it must be unique in the database. .

The query that’s causing this is as follows.

UPDATE users

SET EmailAddress = @emailaddress

WHERE

(

  RecID = @recid AND fk_Sites_RecID = @fk_sites_recid

  AND

  (

    /* Allow an update when address is being updated with a blank */

    ( @emailaddress = '' )

    /* Address isn't blank, so test it doesn't exist elsewhere */

    OR NOT EXISTS

    (

      SELECT * FROM users

      WHERE

      NOT ( RecID = @recid AND fk_Sites_RecID = @fk_sites_recid )

      AND RTRIM(LTRIM(COALESCE(EmailAddress,''))) = @emailaddress

    )

  )

)

Answer

In SQL Server, an after update trigger will be fired as long as the update statement completed successfully, even if no records were actually updated.

From the CREATE TRIGGER MSDN page:

An AFTER trigger is executed only after the triggering SQL statement has executed successfully.

So, you must check inside your trigger if the relevant data has indeed changed, by comparing the data between the inserted and the deleted tables.

Here is a quick example:

Create sample tables and trigger:

CREATE TABLE [dbo].[t] (
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [text] [char](1) NOT NULL
);


CREATE TABLE [dbo].[t2](
    [id] [int] NOT NULL,
    [t] [char](1) NOT NULL
);


CREATE TRIGGER [dbo].[t_forupdate]
   ON  [dbo].[t]
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO t2 
    SELECT id, [text]
    FROM t 

END

insert sample data:

INSERT INTO T VALUES ('a');

Run an update statement that will not change anything in the table

UPDATE T 
SET [text] = 'b'
WHERE 1 = 0;

select from the sample tables:

SELECT *
FROM T2 

SELECT *
FROM T

Results:

t
id          t
----------- ----
1           a


t2
id          text
----------- ----
1           a