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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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