Trigger firing despite no rows being affected

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
10
down vote

favorite

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?

edited Aug 13 at 13:13

Andriy M

15.3k53470

asked Aug 13 at 12:30

LuÃs Gabriel de Andrade

636

add a commentÂ |Â

up vote
10
down vote

favorite

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

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?

edited Aug 13 at 13:13

Andriy M

15.3k53470

asked Aug 13 at 12:30

LuÃs Gabriel de Andrade

636

add a commentÂ |Â

up vote
10
down vote

favorite

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

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?

edited Aug 13 at 13:13

Andriy M

15.3k53470

asked Aug 13 at 12:30

LuÃs Gabriel de Andrade

636

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

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?

edited Aug 13 at 13:13

Andriy M

15.3k53470

asked Aug 13 at 12:30

LuÃs Gabriel de Andrade

636

edited Aug 13 at 13:13

Andriy M

15.3k53470

edited Aug 13 at 13:13

Andriy M

15.3k53470

edited Aug 13 at 13:13

Andriy M

15.3k53470

asked Aug 13 at 12:30

LuÃs Gabriel de Andrade

636

asked Aug 13 at 12:30

LuÃs Gabriel de Andrade

636

asked Aug 13 at 12:30

LuÃs Gabriel de Andrade

636

add a commentÂ |Â

1 Answer
1

active

oldest

votes

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.

edited Aug 14 at 5:23

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214777%2ftrigger-firing-despite-no-rows-being-affected%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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.

edited Aug 14 at 5:23

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

add a commentÂ |Â

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.

edited Aug 14 at 5:23

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

add a commentÂ |Â

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.

edited Aug 14 at 5:23

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

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.

edited Aug 14 at 5:23

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

edited Aug 14 at 5:23

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

answered Aug 13 at 13:10

ypercubeÃ¡ÂµÂ€Ã¡Â´Â¹

71.4k11118194

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky