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?
Best Answer
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: