在INSERT INTO表(等)SELECT * FROM Table2中,如何在每个插入的行上触发触发器? [英] How can I get a trigger to fire on each inserted row during an INSERT INTO Table (etc) SELECT * FROM Table2?
问题描述
我一直在尝试避免在这种特殊情况下使用光标,因为我不喜欢权衡,只是发生在我使用的过程中,使触发器看起来像正确的操作过程。
I've been trying to avoid using a cursor in this particular case just because I dislike the tradeoffs, and it just so happens a process I'm using makes triggers look like the proper course of action anyway.
存储过程基于复杂的子句组合插入记录,使用插入触发器,我向发送电子邮件的用户告知他们访问某个站点。这很简单,工作正常。
A stored procedure inserts a record based off of a complicated mix of clauses, using an insert trigger I send an email to the target user telling them to visit a site. This is easy and works fine.
但是,另一个过程是每晚运行并重新分发所有未查看的记录。我这样做的方法是根据从分配的日期字段中的select选择另一个插入。说明:
However, another procedure is to run nightly and redistribute all unviewed records. The way I was doing this was to do another insert based on a select on a date field from when it was assigned. To wit:
INSERT INTO Table (ID, User, AssignDate, LastActionDate)
SELECT
ID
,User
,GETDATE() [AssignDate]
,GETDATE() [LastModifiedDate]
FROM Table2
/*snip*/
触发器适用于单个插入,但上面的select语句仅适用于最后插入的行。有没有办法摆脱这种行为?它破坏了整个事情!
The trigger works on individual inserts, but the select statement above only works on the last inserted row. Is there a way to get around this behavior? It ruins the whole thing!
编辑(触发代码):
ALTER TRIGGER dbo.Notify
ON dbo.Table
AFTER INSERT
AS
BEGIN
DECLARE @EmailSender varchar(50)='Sender Profile'
DECLARE @Identity int
DECLARE @User varchar(20)
DECLARE @Subject varchar(50)
SET @Identity=@@Identity
SELECT @User=User, @Subject='(' + CONVERT(varchar,@Identity) + ')!'
FROM Table
WHERE
idNum=@Identity
exec msdb.dbo.sp_send_dbmail
@profile_name=@EmailSender,
@recipients=@User
@subject=@Subject,
@body='//etc'
END
推荐答案
对于批量插入,插入触发器被调用一次,但是在触发器上,您可以使用特殊的插入的
表来获取所有插入的行。
The insert trigger is called once for bulk inserts, but on the trigger you can use the special inserted
table to get all the inserted rows.
所以,想象你h ave这样的插入触发器,记录插入到表中的所有行
So, imagine you have an insert trigger like this one, that logs all the rows inserted into table
create trigger trgInsertTable
on dbo.table
for insert
as
insert tableLog(name)
select name from inserted
使用此触发器,当您在表
上批量插入时, tableLog
填充的插入到表的相同行数
With this trigger, when you make a bulk insert on table
, the tableLog
is filled with the same number of rows that were inserted to table
对于特定的触发器,由于您需要为每行调用存储过程,因此需要使用游标:
For you specific trigger, since you need to call a stored procedure for each row, you need to use a cursor:
ALTER TRIGGER dbo.Notify
ON dbo.Table
AFTER INSERT
AS
BEGIN
DECLARE @EmailSender varchar(50)='Sender Profile'
DECLARE @User varchar(20)
DECLARE @Subject varchar(50)
DECLARE cursor CURSOR FOR
SELECT User, '(' + CONVERT(varchar, Id) + ')!'
FROM inserted
OPEN cursor
FETCH NEXT FROM cursor INTO @User, @Subject
WHILE @@FETCH_STATUS = 0
BEGIN
exec msdb.dbo.sp_send_dbmail
@profile_name=@EmailSender,
@recipients=@User
@subject=@Subject,
@body='//etc'
FETCH NEXT FROM cursor INTO @User, @Subject
END
CLOSE cursor
DEALLOCATE cursor
END
我没有测试,但应该工作
I didn't tested, but should work
这篇关于在INSERT INTO表(等)SELECT * FROM Table2中,如何在每个插入的行上触发触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!