StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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</p> <ul> <li>put the MERGE into a support SP that the main one calls; or</li> <li>use dynamic SQL</li> </ul> <h2>Dynamic SQL</h2> <p>Let's create a table with a trigger</p> <pre><code>create table tg1(i int) ; create trigger tg1_tg on tg1 instead of insert as select 1 GO </code></pre> <p>Then attempt to MERGE on the table</p> <pre><code>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 ; </code></pre> <p>Not good..</p> <blockquote> <p>Msg 5316, Level 16, State 1, Line 1<br> 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.</p> </blockquote> <p>So we use dynamic SQL</p> <pre><code>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 ; </code></pre> <h2>Support procedure</h2> <p>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)</p> <pre><code>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 </code></pre> <p>No go...</p> <blockquote> <p>Msg 5316, Level 16, State 1, Line 1<br> 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.</p> </blockquote> <p>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.</p> <p>Finally, you can run this batch which works</p> <pre><code>alter table tg1 disable trigger tg1_tg ; exec tg1_MERGE ; alter table tg1 enable trigger tg1_tg ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload