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?

up vote 24 down vote accepted

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.