sql server 2012 merge的使用，同構表merger,異構表merge, 當merge 與trigger 同時使用時較容易出錯

標籤：des   style   blog   http   io   ar   os   使用   for   

來看看老外的例子，例子有不正確的地方，

 http://www.kodyaz.com/t-sql/sql-merge-command-for-summary-table.aspx

Create Trigger trMergeProductSummary on ProductDetails After Insert, Update, DeleteASBEGIN;Merge ProductSummaryUsing ( SELECT  i.ProductId,  i.ProductName,  i.ProductNumber,  i.CategoryId,  i.ContactPerson,  i.UpdateDate from inserted i) MergeData ON ProductSummary.ProductId = MergeData.ProductId WHEN MATCHED THEN UPDATE SET  ProductSummary.ProductName = MergeData.ProductName,  ProductSummary.ProductNumber = MergeData.ProductNumber,  ProductSummary.CategoryId = MergeData.CategoryId,  ProductSummary.ContactPerson = MergeData.ContactPerson,  ProductSummary.UpdateDate = GetDate() WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (ProductId, ProductName, ProductNumber, CategoryId, ContactPerson, GetDate()) WHEN NOT MATCHED BY SOURCE THEN DELETE;ENDGO

當運行下面語句時， ProductSummary只有一條記錄:而不是我們期望的三條

insert into ProductDetails (productname, productnumber, contactperson) values ('SQL Data Compare Tool', 'SQL-DC-001', 'Eralper Yilmaz') go 3select * from ProductDetailsselect * from ProductSummary

只有一條記錄:而不是我們期望的三條

當運行下面語句時，ProductSummary 只有一條記錄:而不是我們期望的三條
當運行下面語句時，ProductSummary 中的資料被消除：

注意，

當delete時trigger 中的inserted沒有資料，所以會清空ProductSummary 表：

WHEN NOT MATCHED BY SOURCE THEN DELETE--source is empty, so it will clear all data in table ProductSummary 

下面是merge trigger 的實現

IF OBJECT_ID('[gomac].[T_INSERT_AvSysCodes]', 'TR') IS NOT NULLBEGIN    DROP TRIGGER [gomac].[T_INSERT_AvSysCodes]ENDGO CREATE TRIGGER [gomac].[T_INSERT_AvSysCodes]ON [gomac].[AVSYSCODES]FOR  INSERT, DELETE, UPDATEASset nocount onDECLARE @count        BIGINT = 0,        @rowid        BIGINT = 1,         @sql          NVARCHAR(MAX) = N'',        @sqlmerge     NVARCHAR(3000) = N'', @action       NVARCHAR(20)='',        @RECID        BIGINT  if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp'))DROP TABLE #tmpIF EXISTS(SELECT * FROM   INSERTED) AND NOT EXISTS(SELECT * FROM   DELETED) ----INSERTBEGINSET @action='INSERT'ENDELSE IF NOT EXISTS(SELECT * FROM   INSERTED)AND EXISTS(SELECT * FROM   DELETED) ----DELETEBEGIN   SET @action='DELETE'ENDELSE IF EXISTS(SELECT * FROM   INSERTED) AND EXISTS(SELECT * FROM   DELETED) -----UPDATEBEGIN   SET @action='UPDATE'ENDSELECT row_number()OVER(ORDER BY dataareaid) rowid,DESCRIPTION,code,category,GETDATE() Modifieddatetime,Modifiedby,GETDATE() Createddatetime,CreatedBy,dataareaid,1 Recversion,NULL Recid ,@ACTION AS [ACTION] INTO #tmp FROM (select * from insertedunion select * from deleted) as a        SELECT @[email protected]@rowcountIF @action='INSERT' ----INSERTBEGINWHILE @rowid<[email protected]BEGINEXEC  DBO.UP_Get_Axapta_Seqno 'AvSysCodesAX',@RECID OUTPUTUPDATE #tmp SET [email protected] WHERE [email protected]SET @rowid+=1ENDEND;MERGE dbo.AvSysCodesAX d USING  #tmp s ON d.dataareaid = s.dataareaid AND d.category = s.category AND  d.code = s.code WHEN NOT matched  THEN INSERT ( DESCRIPTION,code,category,Modifieddatetime,Modifiedby,Createddatetime,CreatedBy,dataareaid, Recversion,Recid)VALUES          ( S.DESCRIPTION,S.code,S.category,S.Modifieddatetime,S.Modifiedby,S.Createddatetime,S.CreatedBy,S.dataareaid,S.Recversion,S.Recid) WHEN matched AND S.ACTION='UPDATE' THEN    UPDATESET   d.[DESCRIPTION]    = s.[DESCRIPTION] ,  d.code= s.code,  d.category= s.category,  d.Modifieddatetime= s.Modifieddatetime,  d.Modifiedby    = s.Modifiedby,  d.dataareaid= s.dataareaid WHEN  matched AND S.ACTION='DELETE' THEN DELETE ;if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp'))DROP TABLE #tmp  GO 

注意三點：

1. merge的源是第一次觸發的inserted和deleted,這點不同於物理表，datasource 用inserted union deleted
2. 在WHEN  matched AND S.ACTION=‘DELETE‘ THEN delete;
3. 沒有trigger row by row,seem currsor as the last choice.

存用

sql server 2012 merge的使用，同構表merger,異構表merge, 當merge 與trigger 同時使用時較容易出錯