Menu
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
228
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.
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.
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.
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)
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
