Hi Hugo,

Thank you very much for your input. Since I exchanged the messages with Borislav I ran into a wall with the UPDATE trigger. So I am going to have only DELETE trigger. So I am not sure if I need the INSERT trigger. I will have to think about all possible scenarios.


>
>
>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.
>