Peter Larsson Blog

Patron Saint of Lost Yaks

SQL Server 2008 with MERGE and triggers

I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does.
The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements.
According to Books Online, there is no sure way to guarantee the order of "streams" to execute, but it seems SQL Server favors INSERT / UPDATE / DELETE order.
 
CREATE TABLE tTemp
              (
                     i INT,
                     j INT
              )
 
INSERT tTemp
       (
              i,
              j
       )
SELECT 1, 0 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0
 
GO
 
CREATE TABLE tLog
              (
                     rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                     info VARCHAR(200)
              )
 
GO
 
CREATE TRIGGER trgINSERT ON tTemp
AFTER INSERT
AS
 
WAITFOR DELAY '00:00:04'
 
INSERT tLog
SELECT 'INSERT - New value - ' + STR(inserted.i) + STR(inserted.j) + ', ' + CONVERT(CHAR(25), GETDATE(), 121)
FROM   inserted
 
GO
 
CREATE TRIGGER trgUPDATE ON tTemp
AFTER UPDATE
AS
 
WAITFOR DELAY '00:00:04'
 
INSERT tLog
SELECT 'UPDATE - New value - ' + STR(inserted.i) + STR(inserted.j) + ', ' + CONVERT(CHAR(25), GETDATE(), 121)
FROM   inserted
 
UNION ALL
 
SELECT 'UPDATE - Old value - ' + STR(deleted.i) + STR(deleted.j) + ', ' + CONVERT(CHAR(25), GETDATE(), 121)
FROM   deleted
 
GO
 
CREATE TRIGGER trgDELETE ON tTemp
AFTER DELETE
AS
 
WAITFOR DELAY '00:00:04'
 
INSERT tLog
SELECT 'DELETE - Old value - ' + STR(deleted.i) + STR(deleted.j) + ', ' + CONVERT(CHAR(25), GETDATE(), 121)
FROM   deleted
 
GO
 
DECLARE       @time DATETIME
 
SET    @time = GETDATE()
 
MERGE tTemp AS t
USING (
              SELECT 1 AS theValue, 99 AS theAction UNION ALL
              SELECT 2, -1 UNION ALL
              SELECT 20, NULL UNION ALL
              SELECT 50, NULL
       ) AS s ON s.theValue = t.i
WHEN   MATCHED AND s.theAction = -1
          THEN UPDATE SET t.j = t.j + 1
WHEN   NOT MATCHED
          THEN INSERT VALUES (theValue, 0)
WHEN   MATCHED AND s.theAction = 99
          THEN DELETE;

SELECT DATEDIFF(MILLISECOND, @time, GETDATE()) AS [Time taken in milliseconds]
 
SELECT *
FROM   tLog
 
DROP TABLE    tTemp,
              tLog
 

Legacy Comments


Aditya
2009-08-05
re: SQL Server 2008 with MERGE and triggers
Hi i want to know about how to create Triggers So please help me out Of This ????

Selvi
2012-04-03
re: SQL Server 2008 with MERGE and triggers
Nice Article!