Insert multiple records using trigger

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

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

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.

Posted 5-Jul-14 1:09am

Mitul Birla

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2014-07-05T03:49:00

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

SQL

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

Insert multiple records using trigger

1 solution

Solution 1

Add your solution here

Preview 0

Insert multiple records using trigger

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us