在触发器中使用 while 存在会在无限循环中发送查询

Question

>我为插入创建了一个触发器，它工作正常。这会很好地创建触发器：

While (exists(Select Id from #temp))

但是插入查询将进入无限循环。我正在使用 while 存在来一次容纳多个插入。谁能告诉我是什么导致了无限循环？

Create Table sqltutorial.Employee
(
Id int,
Name nvarchar(50),
Salary int,
Gender nvarchar(50),
DepartmentId int
)
Alter Trigger sqltutorial.trg_forinsert_Employee
on sqltutorial.Employee
For Insert
As
Begin
print 'Audit Begins'
Declare @Id int, @Name nvarchar(50), @Salary int,
@Gender nvarchar(50), @DepartmentId nvarchar(50)
Declare @AuditText nvarchar(500)
Select * 
into #temp 
from inserted
While (exists(Select Id from #temp))
Select @Id = Id from #temp
Select 
@Id = Id, @Name = Name, @Salary = Salary,
@Gender = Gender, @DepartmentId = DepartmentID 
from 
#temp
Set @AuditText = 'New Record Inserted With Id='+Cast(@Id As nvarchar(50))+',Name='+@Name+' ,Salary='+CAST(@Salary as nvarchar(50))+' Gender'+@Gender
+' ,Department Id='+@DepartmentId+' on '+CAST((Select GETDATE()) AS nvarchar(50))+' by '+(Select  system_user)
Insert into sqltutorial.AuditTrial 
values (@AuditText)
Delete from #temp 
where Id = @Id
print 'Audit Ends'
End

Answer 1

无限循环的原因是您没有指定BEGIN，并像这样对WHILE循环代码块进行END：

WHILE SomeCondition = true
BEGIN
Do stuff
END

当您使用WHILE并且不指定BEGIN..END时，WHILE循环仅一遍又一遍地重复下一条语句，直到不再满足 WHILE 条件。 在您的代码中，这永远不会发生，因为下一条语句不会从 #temp 中删除任何内容。

换句话说，在您的代码中，这就是您正在循环的内容：

While (exists(Select Id from #temp))
Select @Id = Id from #temp

在此之后的其余代码甚至永远不会执行，因为WHILE循环永远不会退出。

Answer 2

您无需在触发器中使用所有变量和临时表，只需执行以下操作：

ALTER TRIGGER sqltutorial.trg_forinsert_Employee
ON sqltutorial.Employee
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO sqltutorial.AuditTrial 
SELECT  
' New Record Inserted With Id=' + CAST([Id] AS NVARCHAR(50)) +
',Name=' + [Name] +
',Salary=' + CAST(Salary AS NVARCHAR(50)) +
',Gender' + Gender +
',Department Id=' + DepartmentId + 
' on ' + CAST((SELECT GETDATE()) AS NVARCHAR(50)) +
' by ' + CAST(system_user AS NVARCHAR(256))
FROM 
inserted
END