A Mild Annoyance With MERGE Statements And Triggers

I’m No Expert

---

I will leave the finer points of the problems with MERGE statements to the Michaels and Aarons of the SQL Server world.

This is just a… But why? post about them, because I’m honestly a bit puzzled by this missing implementation detail.

To get us to the point, I’m going to use a code snippet (with embellishments) from Aaron’s post here.

We’ll be starting with this table and trigger from the linked post, with a couple small tweaks to satisfy my OCD:

CREATE TABLE 
    dbo.MyTable
(
    id integer
);

INSERT 
    dbo.MyTable 
VALUES
    (1),
    (4);

CREATE OR ALTER TRIGGER 
    dbo.MyTable_All
ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    IF ROWCOUNT_BIG() = 0 RETURN;
    IF TRIGGER_NESTLEVEL() > 1 RETURN;
  
    PRINT 'Executing trigger. Rows affected: ' + RTRIM(@@ROWCOUNT);
    
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am an insert...';
    END;
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am an update...';
    END;
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
      PRINT '  I am a delete...';
    END;
END;

After all, one of the best ways to make sure you get code right is to copy and paste it from the internet.

Quiet On The Set

---

As much as we all love to dunk on MERGE, like cursors, heaps, and UDFs of various types, they did give you some neat options with the OUTPUT clause, like the $action column, and the ability to get columns from other tables involved in the query. You can’t do that with a normal insert, update, or delete when using the OUTPUT clause, though I think it would be cool if we could.

Working a bit with the code linked above, here’s an expansion on it showing the additional OUTPUT capability, but this is also where my annoyance begins.

BEGIN TRANSACTION
    DECLARE
        @t table
    (
        action varchar(6),
        i_id integer,
        d_id integer,
        s_word varchar(5)
    );

    SELECT
        mt.*
    FROM dbo.MyTable AS mt;

    MERGE 
        dbo.MyTable WITH (HOLDLOCK) AS Target
    USING 
        (
            VALUES
                (1, 'one'),
                (2, 'two'),
                (3, 'three')
        ) AS Source (id, word)
    ON Target.id = Source.id
    WHEN MATCHED 
    THEN UPDATE 
           SET Target.id = Source.id
    WHEN NOT MATCHED 
    THEN INSERT
           (id) 
         VALUES
           (Source.id)
    WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
    OUTPUT
        $action,
        Inserted.id,
        Deleted.id,
        Source.word
    INTO @t 
    ( 
        action, 
        i_id, 
        d_id,
        s_word
    );
    
    SELECT
        t.*
    FROM @t AS t;

    SELECT
        mt.*
    FROM dbo.MyTable AS mt;
ROLLBACK TRANSACTION;

You likely can’t guess what I’m sore about just looking at this, because this isn’t what annoys me.

This is all fine, and rather a nice showing of capabilities for an oft-maligned (by myself included) bit of syntax.

The problem is really in the trigger.

What’s Wrong With Triggers?

---

If you work with trigger code enough, you’ll get used to seeing:

• Catch all triggers that do a lot of gymnastics to differentiate insert from update from delete
• A few separate triggers to catch each modification type separately, and still do some checking to verify

The problem is that in any trigger, the $action column is not directly exposed for use to determine the action of a MERGE statement.

Sure, you can add a column to a table to track it, or some other hacky workaround, but I consider it a quite poor design choice to not have the $action column as a part of the Inserted and Deleted virtual tables.

Having it there would also benefit generic modifications that are captured by triggers in some manner to make the absolute type of modification quite clear to query writers.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.