Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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
 

Print | posted on Monday, November 24, 2008 2:38 PM | Filed Under [ SQL Server 2008 Administration ]

Feedback

Gravatar

# 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 ????
8/5/2009 1:44 PM | Aditya
Gravatar

# bvcx

dsfds sf ers we e we w w w4err
2/6/2012 2:04 PM | vcbb
Gravatar

# re: SQL Server 2008 with MERGE and triggers

Nice Article!
4/3/2012 11:49 AM | Selvi
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET