>Very interesting! In other words, we can not have only 1 INSTEAD OF trigger defined on a table, we need to have all 3, as BOL suggests, correct?

1 single trigger for the 3 cases it is sufficient

INSTEAD OF INSERT,UPDATE,DELETE

However, if the merge does not declare a case (insert or delete or update) the trigger instead for that case can not be.

>
>>>I hope putting both Delete and Update triggers is an accepted practice (as I hope that a DBA will not look down on such practice). But as far as minimizing maintenance, I like this approach.
>>
>>
>>Hi Dmitry,
>>
>>Borislav will know better than me, so I am cc'ing him, but I think that you should have an INSTEAD OF INSERT trigger too, otherwise in the future attempts to use MERGE will fail as it requires all INSTEAD OF's triggers if you set up one, I learned it the hard way :)
>>
>>From BOL:
>>
>>Trigger Implementation
>>
>>
>>For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.
>>
>>If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.
>>
>>If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.
>>
>>If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.
>>