使用触发器插入多个记录 [英] Insert multiple records using trigger

问题描述

大家好我在删除table1数据时使用以下触发器将数据插入到table2中：

hi guys I am using the following trigger to insert data into table2 when table1 data is deleted:

CREATE TRIGGER trgInsRejctWrd
   ON  table1
   AFTER DELETE
AS

        declare @id int;
        declare @word nvarchar(50);
        declare @type nvarchar(50);
        declare @rejecter nvarchar(15);
BEGIN
            select @word=d.Suggest_SourceWord from deleted d
            select @type=d.Suggest_WordDiff from deleted d
            select @id=ISNULL(max(Reject_ID),0)+1 from table2

            insert into table2(Reject_ID,Reject_Words,Reject_DateTime,Reject_WordType)
            values(@id,@word,GETDATE(),@type)


END
GO

现在它删除s时工作正常ingle记录，但当我删除多个记录时，它只插入

1记录。我想将table1中所有已删除的记录插入到table2中。

所以任何建议都会有所帮助。

提前谢谢。

now it works fine when I delete single record, but when I delete multiple records it insert only
1 record. I want to insert all the deleted records from table1 into table2.
so any suggestions will be helpful.
Thanks in advance.

推荐答案

首先，为table2生成ID的方法是错误的。使用 IDENTITY [ ^ ]列自动为您创建该ID。



其次，你只是将这些变量分配一次，这就是为什么它只插入一条记录，你需要选择整个删除的表。



例如（注意，这假设您正在使用table2的标识列.Reject_id

Firstly your way of generating an ID for table2 is wrong. Use an IDENTITY[^] column to automatically create that ID for you.

Secondly, you are only assigning those variables once, which is why it only inserts a single record, you need to select the entire deleted table.

For example (NB this assumes that you are using an identity column for table2.Reject_id

CREATE TRIGGER trgInsRejctWrd
   ON  table1 AFTER DELETE
AS
     insert into table2
     VALUES(Suggest_SourceWord, GETDATE(), Suggest_WordDiff)
     from deleted

这篇关于使用触发器插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！