sql server 2012 merge的使用，同构表merger,异构表merge, 当merge 与trigger 同时使用时较容易出错

外热呃

    Create Trigger trMergeProductSummary on ProductDetails  
     After Insert, Update, Delete  
    AS  
    BEGIN  
      
    ;Merge ProductSummary  
    Using (  
     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;  
      
    END  
    GO  


当运行下面语句时，ProductSummary只有一条记录:而不是我们期望的三条



    insert into ProductDetails (productname, productnumber, contactperson)  
     values ('SQL Data Compare Tool', 'SQL-DC-001', 'Eralper Yilmaz')  
     go 3  
    select * from ProductDetails  
    select * 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 NULL  
    BEGIN  
        DROP TRIGGER [gomac].[T_INSERT_AvSysCodes]  
    END  
    GO   
    CREATE TRIGGER [gomac].[T_INSERT_AvSysCodes]  
    ON [gomac].[AVSYSCODES]  
    FOR  INSERT, DELETE, UPDATE  
    AS  
    set nocount on  
    DECLARE @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 #tmp  
    IF EXISTS(SELECT * FROM   INSERTED) AND NOT EXISTS(SELECT * FROM   DELETED) ----INSERT  
    BEGIN  
            SET @action='INSERT'  
    END      
    ELSE IF NOT EXISTS(SELECT * FROM   INSERTED)AND EXISTS(SELECT * FROM   DELETED) ----DELETE  
    BEGIN  
           SET @action='DELETE'  
    END  
    ELSE IF EXISTS(SELECT * FROM   INSERTED) AND EXISTS(SELECT * FROM   DELETED) -----UPDATE  
    BEGIN  
           SET @action='UPDATE'  
    END  
    SELECT row_number()OVER(ORDER BY dataareaid) rowid,DESCRIPTION,cast (code as nvarchar)code,category,GETDATE() Modifieddatetime,Modifiedby,GETDATE() Createddatetime,CreatedBy,dataareaid,1 Recversion,cast(0.0 as bigint) Recid ,@ACTION AS [ACTION] INTO #tmp FROM inserted  
         
    SELECT @count=@@rowcount  
    IF @action<>'DELETE' ----INSERT  
    BEGIN        
    If @action='INSERT'      
    WHILE @rowid<=@count  
        BEGIN   
            EXEC  DBO.UP_Get_Axapta_Seqno 'AvSysCodesAX',@RECID OUTPUT  
            UPDATE #tmp SET recid=@recid WHERE rowid=@rowid  
            SET @rowid+=1  
        END      
      
      
    ;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      
            UPDATE    SET            
                 d.[DESCRIPTION]    = s.[DESCRIPTION] ,   
                 d.code                = s.code,   
                 d.category            = s.category,   
                 d.Modifieddatetime    = s.Modifieddatetime,   
                 d.Modifiedby        = s.Modifiedby,   
                 d.dataareaid        = s.dataareaid   ;  
    END  
    else  
    begin  
        delete d from dbo.AvSysCodesAX d where exists(  
            select 1 from deleted s  where d.dataareaid = s.dataareaid AND d.category = s.category AND  d.code = s.code      
        )  
    end  
      
      
    if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp'))  
    DROP TABLE #tmp   
    GO   

注意三点：

    merge的源是第一次触发的inserted和deleted,这点不同于物理表，datasource 用inserted union deleted
    在WHEN  matched AND S.ACTION='DELETE' THEN delete;
    没有trigger row by row,seem currsor as the last choice.

存用