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

来看看老外的例子，例子有不正确的地方，

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

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, 
        @action       NVARCHAR(20)='',
        @RECID        BIGINT  
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
IF @action='DELETE' 
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
else
begin
 if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp'))
    DROP TABLE #tmp
  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='INSERT' or(@action='UPDATE' AND EXISTS(SELECT 1 FROM #TMP S LEFT JOIN  dbo.AvSysCodesAX D ON d.dataareaid = s.dataareaid AND d.category = s.category AND  d.code = s.code WHERE D.code IS NULL ))
	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;
  if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp'))
    DROP TABLE #tmp
end
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.

存用