dimanche 30 novembre 2014

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?





Aucun commentaire:

Enregistrer un commentaire