Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is insert trigger called when there is no data to insert?

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

like image 228
kwitee Avatar asked Mar 13 '13 08:03

kwitee


People also ask

What is instead of insert trigger?

The INSTEAD OF triggers are the DML triggers that are fired instead of the triggering event such as the INSERT, UPDATE or DELETE events. So, when you fire any DML statements such as Insert, Update, and Delete, then on behalf of the DML statement, the instead of trigger is going to execute.

What is insert trigger in SQL?

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run 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.

What is meant by triggers explain a after insert trigger with an example?

Trigger: A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.


1 Answers

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)

like image 50
Damien_The_Unbeliever Avatar answered Sep 27 '22 22:09

Damien_The_Unbeliever