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