sql server merge 实现 trigger t_merge_gomac_avpickmap

IF OBJECT_ID('t_merge_gomac_avpickmap', N'TR') IS NOT NULL
BEGIN
    PRINT 'Dropping Trigger t_merge_gomac_avpickmap'
    DROP TRIGGER t_merge_gomac_avpickmap
    IF @@ERROR = 0
        PRINT 'Trigger t_merge_gomac_avpickmap dropped'
END
GO
CREATE TRIGGER [dbo].[t_merge_gomac_avpickmap]
ON dbo.avpickmap
   AFTER INSERT, UPDATE,DELETE
AS
BEGIN
	SET NOCOUNT ON;
	--organize source data
	;
	WITH s AS (
	          SELECT [AVELINE],[AVSUFFIXID],[ITEMID],[AVZONE],[AVSTATION],
	                 [AVBIN],[AVPACKER],[AVCARTONTYPEID],[AVPIECESPERPACK],
	                 [AVSEQUENCE],[AVFLOORID],[AVPRODUCTGROUP],[AVBLINE],
	                 [AVOFFLINE],[MODIFIEDDATETIME],[DEL_MODIFIEDTIME],
	                 [MODIFIEDBY],[MODIFIEDTRANSACTIONID],[CREATEDDATETIME],
	                 [DEL_CREATEDTIME],[CREATEDTRANSACTIONID],[DATAAREAID],
	                 [RECVERSION],[PARTITION],[RECID],CAST([modifieddatetime] AS DATE)
	                 [modifieddate],DATEDIFF(second,CAST([modifieddatetime] AS DATE), 
					 [modifieddatetime] ) AS [modifiedtime], CAST([createddatetime] AS DATE) [createddate],
					 DATEDIFF(second, CAST([createddatetime] AS DATE), [createddatetime]) AS [createdtime]
	                 FROM dbo.avpickmap
	     )
	     --begin merge gomac.avpickmap
	     MERGE INTO gomac.avpickmap d USING s
	           ON d.RECID = S.RECID
		WHEN matched  AND ( S.[modifiedtime]<>D.[modifiedtime] OR S.[modifieddate]<>D.[modifieddate]) THEN
		UPDATE 	SET    D.[AVELINE]			=S.[AVELINE],
				D.[AVSUFFIXID]              =S.[AVSUFFIXID],
				D.[ITEMID]                  =S.[ITEMID],
				D.[AVZONE]                  =S.[AVZONE],
				D.[AVSTATION]               =S.[AVSTATION],
				D.[AVBIN]                   =S.[AVBIN],
				D.[AVPACKER]                =S.[AVPACKER],
				D.[AVCARTONTYPEID]          =S.[AVCARTONTYPEID],
				D.[AVPIECESPERPACK]         =S.[AVPIECESPERPACK],
				D.[AVSEQUENCE]              =S.[AVSEQUENCE],
				D.[AVFLOORID]               =S.[AVFLOORID],
				D.[AVPRODUCTGROUP]          =S.[AVPRODUCTGROUP],
				D.[AVBLINE]                 =S.[AVBLINE],
				D.[AVOFFLINE]               =S.[AVOFFLINE],
				D.[MODIFIEDDATETIME]        =S.[MODIFIEDDATETIME],
				D.[DEL_MODIFIEDTIME]        =S.[DEL_MODIFIEDTIME],
				D.[MODIFIEDBY]              =S.[MODIFIEDBY],
				D.[MODIFIEDTRANSACTIONID]   =S.[MODIFIEDTRANSACTIONID],
				D.[CREATEDDATETIME]         =S.[CREATEDDATETIME],
				D.[DEL_CREATEDTIME]         =S.[DEL_CREATEDTIME],
				D.[CREATEDTRANSACTIONID]    =S.[CREATEDTRANSACTIONID],
				D.[RECVERSION]              =S.[RECVERSION],
				D.[modifieddate]            =S.[modifieddate],
				D.[modifiedtime]            =S.[modifiedtime]
		WHEN NOT matched THEN
		INSERT ( [AVELINE],[AVSUFFIXID],[ITEMID],[AVZONE],[AVSTATION],[AVBIN],
			   [AVPACKER],[AVCARTONTYPEID],[AVPIECESPERPACK],[AVSEQUENCE],
			   [AVFLOORID],[AVPRODUCTGROUP],[AVBLINE],[AVOFFLINE],[MODIFIEDDATETIME],
			   [DEL_MODIFIEDTIME],[MODIFIEDBY],[MODIFIEDTRANSACTIONID],
			   [CREATEDDATETIME],[DEL_CREATEDTIME],[CREATEDTRANSACTIONID],
			   [DATAAREAID],[RECVERSION],[PARTITION],[RECID],[modifieddate],
			   [modifiedtime],[createddate],[createdtime])
		VALUES
				( S.[AVELINE],S.[AVSUFFIXID],S.[ITEMID],S.[AVZONE],S.[AVSTATION],S.[AVBIN],S.[AVPACKER],
				S.[AVCARTONTYPEID],S.[AVPIECESPERPACK],S.[AVSEQUENCE],S.[AVFLOORID],S.[AVPRODUCTGROUP],
				S.[AVBLINE],S.[AVOFFLINE],S.[MODIFIEDDATETIME],S.[DEL_MODIFIEDTIME],S.[MODIFIEDBY],
				S.[MODIFIEDTRANSACTIONID],S.[CREATEDDATETIME],S.[DEL_CREATEDTIME],S.[CREATEDTRANSACTIONID],
				S.[DATAAREAID],S.[RECVERSION],S.[PARTITION],S.[RECID],S.[modifieddate],S.[modifiedtime],
				S.[createddate],S.[createdtime])
		WHEN NOT MATCHED BY SOURCE THEN DELETE
		;--end merge
END
GO