SQL Server 触发器批量数据行处理

SQL Server的触发器在数据批量处理时，只会触发一次触发器，后面的操作触发器全部失效，SQL Server执行语句并不是按行语句进行，所以在批量处理数据时，触发器需要特殊处理。

处理方式1：
    在触发器内新建一张临时表，将所有数据暂存在临时表（建议加上一个自增主键）中，循环临时表来触发触发器。
    下面是一个Demo，批量插入数据，判断Age是否大于150，若大于数据回滚。

USE [BlogDemo]
GO
/****** Object:  Trigger [dbo].[Trigger_Insert]    Script Date: 2019/5/29 10:52:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
AS 
BEGIN
	
	 SET NOCOUNT ON;
     --新建一张临时表，并加入一个自增主键ID，便于后面的循环操作
	 Create table #temp(ID int IDENTITY(1,1) PRIMARY KEY, PersonID int,Name varchar(50),Age int, AddDate datetime)
 
	 Insert into #temp(PersonID, Name, Age, AddDate)
	 		Select ID, Name, Age, AddDate From inserted
    
	 Declare @i Int=1,@is Int=0
	 SELECT @is =MAX([ID]) FROM #temp

	 WHILE @i<@is
	 	BEGIN
	 		Declare @age int =0;
	 		Select @age=Age From #temp Where ID=@i

	 		IF(@age<150)
	 			Begin
	 				Insert into PersonLog(PersonID, Name, Age, AddDate)
	 				Select ID, Name, Age, AddDate From #temp Where ID=@i
	 			End
	 		ELSE
	 			Begin
	 				print('年龄应小于150')
	 				rollback transaction
	 			END

	 		SET @i=@i+1;
	 	END
	 	
END

---

处理方式2：
    在触发器内使用游标来循环数据触发触发器。
    同样的Demo如下：

USE [BlogDemo]
GO
/****** Object:  Trigger [dbo].[Trigger_Insert]    Script Date: 2019/5/29 10:52:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;
   
   --游标也可以实现行机触发器
	   Declare insert_cursor cursor Forward_Only static
		For Select ID,Name,Age,AddDate from inserted
	
		open insert_cursor
	
		Declare @Id int, @Name varchar(50), @Age int,@AddDate datetime
		Fetch From insert_cursor into @Id, @Name, @Age, @AddDate
	
		while @@FETCH_STATUS=0
		  Begin
			IF(@Age>150)
				 Begin
					print('年龄应小于150')
					Rollback transaction 
				 END
			ELSE
				Insert Into PersonLog(PersonID,Name,Age,AddDate)
				 VALUES
				(@Id, @Name, @Age, @AddDate)
		    
			Fetch Next From insert_cursor into  @Id, @Name, @Age, @AddDate
		  END
	  
		close insert_cursor	
		deallocate insert_cursor

END

Demo测试数据图：