SQL Server 2008 MERGE statement - how to disable the INSTEAD OF INSERT trigger to enable MERGE

Question

I am trying to use the SQL SERVER 2008 MERGE statement in a stored procedure to update / insert a table. I have an INSTEAD OF INSERT trigger in a table and I get the following error message when trying to CREATE a procedure

The target “Phone” of the MERGE operator has an INSTEAD OF trigger for some, but not all, of the actions specified in the MERGE statement. In the MERGE statement, if any action has an INSTEAD OF trigger activated for the target, all actions must include INSTEAD OF triggers.

I definitely do not need the INSTEAD OF UPDATE trigger (and cannot create it because CASCADE DELETES is included in the table).

So, in the stored procedure, I first issue the DISABLE TRIGGER command before MERGE. But when I run the saved proc, I get the same error as the DISABLE TRIGGER command never runs.

Answer 1

The query optimizer does a static analysis of your T-SQL batch, and as soon as it sees the MERGE statement, it will check the requirements. It will NOT affect any DDL statements that affect triggers before the MERGE statement.

You can get around this by using GO to split the operators into separate batches, but if it is in the same SP (without GO statements), you have two options

• puts MERGE in the support SP, which calls the main call; or
• use dynamic SQL

Dynamic SQL

Let me create a table with a trigger

create table tg1(i int) ; create trigger tg1_tg on tg1 instead of insert as select 1 GO 

Then try merge in the table

 alter table tg1 disable trigger tg1_tg ; merge tg1 as target using (select 1 union all select 3) as source (X) on target.i = source.x when matched then delete when not matched by target then insert (i) values (x) output $action, inserted.*, deleted.* ; alter table tg1 enable trigger tg1_tg ; 

Not good..

Msg 5316, Level 16, State 1, Line 1
The target 'tg1' of the MERGE statement has an INSTEAD OF trigger for some, but not all, of the actions specified in the MERGE statement. In the MERGE statement, if any action has an INSTEAD OF trigger activated for the target, all actions must include INSTEAD OF triggers.

So we use dynamic SQL

 alter table tg1 disable trigger tg1_tg ; exec (' merge tg1 as target using (select 1 union all select 3) as source (X) on target.i = source.x when matched then delete when not matched by target then insert (i) values (x) output $action, inserted.*, deleted.* ;') alter table tg1 enable trigger tg1_tg ; 

Support procedure

Let me create a procedure that will execute MERGE (the production process will probably have a table variable, use the #temp table, or take some parameters)

 create proc tg1_MERGE as merge tg1 as target using (select 1 union all select 3) as source (X) on target.i = source.x when matched then delete when not matched by target then insert (i) values (x) output $action, inserted.*, deleted.* ; GO 

Do not leave...

Msg 5316, Level 16, State 1, Line 1
The target 'tg1' of the MERGE statement has an INSTEAD OF trigger for some, but not all, of the actions specified in the MERGE statement. In the MERGE statement, if any action has an INSTEAD OF trigger activated for the target, all actions must include INSTEAD OF triggers.

Even to create it, you need to disable triggers - so turn off the trigger and create the process again - it will work this time.

Finally, you can run this batch that works

 alter table tg1 disable trigger tg1_tg ; exec tg1_MERGE ; alter table tg1 enable trigger tg1_tg ;