Trigger firing despite no rows being affected

Question

This is more of a general question but the motivation for this question was a problem I faced while using SQL Server.

I have this trigger attached to an Insert event on a table that contains some logic that, as a side effect, would raise error if no rows were inserted. Upon further investigation, I discovered that the trigger was firing despite no rows being inserted.

The language used in the Microsoft Docs on DML Triggers seems to contradict this behavior:

DML triggers is a special type of stored procedure that automatically takes effect when a DML event takes place that affects the table or view defined in the trigger.

Is this a default behavior across DBMSs? Is there a particular reason to fire a trigger when no rows are affected?

Answer 1

For DML actions, there are row based and statement based triggers.

• Row triggers fire when (before, after or instead of) each row is affected (inserted/updated/deleted). So they will fire 100 times if 100 rows are affected and not at all if 0 rows are affected.

• Statement triggers fire when an INSERT / UPDATE / DELETE statement is executed. It doesn't matter if no rows are affected. Statement level triggers fire anyway and only once for a statement (whether it's 0, 100 or a billion rows affected).

Some DBMS have only row level triggers (MySQL).

Others (like SQL Server^* which is your DBMS) have only statement level triggers.

Some others (DB2, Oracle, Postgres) have both kinds of triggers.

---

^* SQL Server CREATE TRIGGER docs state:

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.