SQL server 触发器，在触发Merge过程中，逐行触发的解决办法 用group by 避免是一次触发中的多行更新或删除。

When I share "tsql trigger row by row" on bing.

I got :点击打开链接

Trigger:

IF OBJECT_ID('dbo.UPD_ORDERTRACKING_WEBE','TR') IS NOT NULL
BEGIN 
	DROP TRIGGER dbo.UPD_ORDERTRACKING_WEBE
END
GO
CREATE TRIGGER  dbo.UPD_ORDERTRACKING_WEBE
   ON dbo.avORDERTRACKING
   AFTER  INSERT,UPDATE
AS 
BEGIN
	/*SET NOCOUNT ON added to prevent extra result sets from
	 *interfering with SELECT statements.*/
	SET NOCOUNT ON;
		--organize source data
		;WITH s AS (
		 SELECT INSERTED.dataareaid AS dataareaid,INSERTED.CustAccount AS accountnum,
					GETDATE() AS refresh_ts,MAX(INSERTED.recid) recid,MAX(INSERTED.RECVERSION) 
					RECVERSION,MAX(INSERTED.PARTITION) PARTITION FROM 
					INSERTED WHERE  INSERTED.avorderstatus <> 'DELETED' 
			 GROUP BY INSERTED.dataareaid,INSERTED.CustAccount	
			 --for records of this set maybe not unique,using 'group by' can prevent updating or deleting the same row twice.
		)
		--begin merge AVCUSTTABLE_WEBE_LOG
		MERGE INTO AVCUSTTABLE_WEBE_LOG d USING s
		ON d.dataareaid = s.dataareaid AND d.accountnum = s.accountnum
		WHEN matched THEN
			UPDATE SET    d.refresh_ts = s.refresh_ts     
		WHEN NOT matched THEN
			INSERT ( DATAAREAID,ACCOUNTNUM,REFRESH_TS,RECID,PARTITION,RECVERSION)VALUES
			( s.DATAAREAID,s.ACCOUNTNUM,s.REFRESH_TS,s.recid,S.PARTITION,s.RECVERSION)
		;--end merge
END
GO

My test case:

1. Inserting 4111 records,it costs zero second.
Insert records  into avordertrancking.
 
Check the statistics, it costs 2+35+37=74ms to insert 4111 records into avordertrancking.
 
Check the result set, it has inserted.
 
2. Updating 4111 records,it costs zero ms
Update all  records  in avordertrancking. it costs 7+18+11=36ms
 
Check the result set ,it has updated
 
3. Additional It raise error without grouping

IF OBJECT_ID('dbo.UPD_ORDERTRACKING_WEBE','TR') IS NOT NULL
BEGIN 
	DROP TRIGGER dbo.UPD_ORDERTRACKING_WEBE
END
GO
CREATE TRIGGER  dbo.UPD_ORDERTRACKING_WEBE
   ON dbo.avORDERTRACKING
   AFTER  INSERT,UPDATE
AS 
BEGIN
	/*SET NOCOUNT ON added to prevent extra result sets from
	 *interfering with SELECT statements.*/
	SET NOCOUNT ON;
		--organize source data
		;WITH s AS (
		 SELECT INSERTED.dataareaid AS dataareaid,INSERTED.CustAccount AS accountnum,
					GETDATE() AS refresh_ts, INSERTED.recid , INSERTED.RECVERSION  
					RECVERSION, INSERTED.PARTITION  FROM 
					INSERTED WHERE  INSERTED.avorderstatus <> 'DELETED'  
			 --for records of this set maybe not unique,using 'group by' can prevent updating or deleting the same row twice.
		)
		--begin merge AVCUSTTABLE_WEBE_LOG
		MERGE INTO AVCUSTTABLE_WEBE_LOG d USING s
		ON d.dataareaid = s.dataareaid AND d.accountnum = s.accountnum
		WHEN matched THEN
			UPDATE SET    d.refresh_ts = s.refresh_ts     
		WHEN NOT matched THEN
			INSERT ( DATAAREAID,ACCOUNTNUM,REFRESH_TS,RECID,PARTITION,RECVERSION)VALUES
			( s.DATAAREAID,s.ACCOUNTNUM,s.REFRESH_TS,s.recid,S.PARTITION,s.RECVERSION)
		;--end merge
END
GO

Here is example contains:

1. 01create apoms database.sql
2. 02dbo.AVCUSTTABLE_WEBE_LOG.Table.sql
3. 03dbo.avORDERTRACKING_bak.Table.sql
4. 04dbo.avordertracking.Table.sql
5. 05BMSSA.UPD_ORDERTRACKING_WEBE.sql
6. 06[APOMS].[dbo].[avORDERTRACKING_bak] 2014-10-29 11-23-36.sql
7. 07test.sql
8. runall.bat

download:

点击打开链接