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

Question

I am attempting to use the SQL SERVER 2008 MERGE statement in a stored procedure for updating/inserting a table. I have an INSTEAD OF INSERT trigger on the table, and I get the following error message when attempting to CREATE the procedure

The target 'Phone' of the MERGE statement has an INSTEAD OF trigger on some, but not all, of the actions specified in the MERGE statement. In a MERGE statement, if any action has an enabled INSTEAD OF trigger on the target, then all actions must have enabled INSTEAD OF triggers.

I definitely do not need an INSTEAD OF UPDATE trigger, (and can't create one because of CASCADE DELETES being enabled on the table).

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

Answer 1

The Query Optimizer does a static parse of your T-SQL batch, and as soon as it sees the MERGE statement, it will validate the requirements. It will NOT factor in any DDL statements that affect the triggers before the MERGE statement.

You can work around this using GO to break the statements into separate batches, but if it is in a single SP (no GO statements), you have two choices

• put the MERGE into a support SP that the main one calls; or
• use dynamic SQL

Dynamic SQL

Let's 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 attempt to MERGE on 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 on some, but not all, of the actions specified in the MERGE statement. In a MERGE statement, if any action has an enabled INSTEAD OF trigger on the target, then all actions must have enabled 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's create a procedure that will perform the MERGE (a production proc probably would have a table variable, use a #temp table or take in 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

No go...

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

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

Finally, you can run this batch which works

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