why is my trigger called even when the underlying table didn't change? I have a quite basic trigger defined as such:

CREATE TRIGGER dbo.foo
ON dbo.bar
FOR INSERT, UPDATE, DELETE
AS
   ...


I also have a stored procedure which at some point does something like this:

Insert into dbo.bar (a) select a from @bar


When @bar contains some data, my trigger is called as expected, but when it doesn't, the trigger is called even though dbo.bar obviously didn't change.

This is confirmed by both SQL profiler and the contents of sys.dm_exec_trigger_stats.

SQL Profiler reports a very low footprint (3 reads, 0 write), but since I have a lot of triggers, this happens a lot, and I end up with significant total_reads figures.

Any hints on why this is happening? Is there a way to change this behavior?
This is the expected behaviour of a statement trigger. It executes every time a statement (INSERT in this case) is executed and once (per statement, not per row.) The behaviour is similar in other DBMS (like Oracle.)

The MSDN says it in the first paragraph in the CREATE TRIGGER page:


  These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.


I don't think there is a way to change this. There are ways to check though - inside the trigger, once it fires - whether there are rows affected or not and act accordingly.

For example you can use

IF ( @@ROWCOUNT = 0 )
  RETURN;


Or a somewhat more robust alternative in the presence of MERGE

IF NOT EXISTS(SELECT *
              FROM   DELETED)
   AND NOT EXISTS(SELECT *
                  FROM   INSERTED)
  RETURN;