SQL Server: triggers: rows affected

Self-tutoring about SQL Server: the tutor mentions a potential peculiarity.

Let’s imagine there’s a trigger that updates a row just inserted. Performing an insert on such a table today, I got the message

Query succeeded: Affected rows: 2

Yet, to my mind, only one row was affected: the freshly inserted one.

I checked the table and found that, in fact, no previous rows had been changed; the only change to it, as hoped, was the newly inserted row. So why would rows affected be 2?

I decided that SQL Server may count each new operation as a change to a row. The insert, then the trigger, it may consider as separate row operations. Therefore, 2 rows are affected means, in this case, that one row is affected twice.

That’s my suspicion, anyway.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Leave a Reply