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.
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
WHEN MATCHED AND s.theAction = 99
THEN DELETE;
SELECT DATEDIFF(MILLISECOND, @time, GETDATE()) AS [Time taken in milliseconds]
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 ???? |
|
vcbb
2012-02-06 |
bvcx dsfds sf ers we e we w w w4err |
|
Selvi
2012-04-03 |
re: SQL Server 2008 with MERGE and triggers Nice Article! |