我需要像在 1000 中使用在一列中具有不同值的触发器一样批量插入行

Question

这是触发

CREATE TRIGGER [dbo].[Teacher]
ON [dbo].[Teacher]
After INSERT
AS
Declare @fid int, @PR NVARCHAR(MAX),@Mycounter  as INT
Select top 1 @fid = eid from human where TypeID = 2 
order by NewID()
Select top 1 @PR = Pid from [dbo].[Program] Where Depid = 1
order by NewID()
Set @Mycounter =1
While @Mycounter <5
BEGIN
Insert Into HeadofDep(SessionID,fid,pid,name,createddate)
Select SessionID, @fid,@PR,NULL,null from INSERTED
Where eid in (Select eid from human where TypeID = 3)
set @MyCounter = @MyCounter + 1; 
END

当在教师表中插入任何行时，我需要在HeadofDep表中插入1000行。我已经应用了循环，但插入到HeadofDep表中的所有行都具有相同的@PR。每排都需要不同的颜色。还需要增加sessionid。我怎样才能做到这一点？

Answer 1

只需增加SessionID，然后将其他内容放入循环中：

Declare @fid int, @PR NVARCHAR(MAX),@Mycounter  as INT
Set @Mycounter =1
While @Mycounter <5
BEGIN
Select top 1 @fid = eid from human where TypeID = 2 
order by NewID()
Select top 1 @PR = Pid from [dbo].[Program] Where Depid = 1
order by NewID()
Insert Into HeadofDep(SessionID,fid,pid,name,createddate)
Select SessionID, @fid,@PR,NULL,null from INSERTED
Where eid in (Select eid from human where TypeID = 3)
set @MyCounter = @MyCounter + 1; 
END

此外，在触发器中执行这样的LOOP是不好的。在这种情况下，你可以用这样一个插件更改1000个插件：

Insert Into HeadofDep(SessionID,fid,pid,name,createddate)
Select SessionID + N, @fid,@PR,NULL,null 
from INSERTED
CROSS APPLY
(
SELECT TOP (1000) -1+row_number() over(order by t1.number) as N
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2
) DS 
Where eid in (Select eid from human where TypeID = 3)