5

I noticed a behavior which comes to me as a very wierd one. If I make an insert-select statement and the select part returns no data, the insert part still executes and even insert trigger is called (in insert trigger there is 0 rows in inserted pseudotable).

Example: 

insert into table1 column1
select column1 from table2 where condition = 'Never met'

Output: 

(0 row(s) affected) // 0 rows inserted
(1 row(s) affected) // log from trigger

May it be caused by 'universal' trigger (declared FOR INSERT, UPDATE, DELETE at once)? I know that this is more like hypothetical question and I have to accept this behavior. But why is this happening? It makes no sense to me. I am using SQL Server 2005.

-- kwitee

kwitee
  • 172
  • 4
  • 13

2 Answers2

9

Because they're documented to do so?

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.

(My emphasis)

Maybe it makes little sense, but maybe it would cost more time and development effort for microsoft to create a special code path that suppresses the triggers when it turns out that there are no rows affected.

It's just another example where you need to design triggers carefully to deal with inserted and deleted potentially containing 0, 1 or many rows.

(Also, from a relational point of view, sets containing no tuples can still be interesting at times)

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • I encountered this question and your answer in time to fix a blatant mistake in one of my answers before many (more) eyes could see it. :) Thanks! – Andriy M Mar 13 '13 at 10:34
  • My question was about why they decided to do that this way. I was looking for maybe another reason than to save development time. But I can't find any, for me it's very unintuitive and makes creating triggers a bit more complicated. But thanks anyway. – kwitee Mar 14 '13 at 09:45
0

A SQL trigger is executed or fired whenever an event that is associated with a table occurs e.g., insert, update or delete and not for select statement. Rewirte the trigger to make sure that table is updated or inserted or delete.

Regards, sa.va3feb

sa.va3feb
  • 11