Trigger insert not working with insert..select from statement

Question

As per topic, the trigger insert operation is about not working when I insert certain records into mcard_list based on below,

SQL Server trigger function:

create trigger cmpnupdatemcard on MP.dbo.mcard_list
For Insert
AS
   declare @est_no varchar(100);
   select @est_no = ins.est_no from inserted ins;

   if NOt exists ( select * from mcard where est_no = @est_no )
   Begin
      insert into mcard select * from mcard_list where est_no = @est_no
      delete from mcard_list where est_no=@est_no
   End
   Else
   Begin
      delete from mcard where est_no = @est_no
      insert into mcard select * from mcard_list where est_no = @est_no
      delete from mcard_list where est_no=@est_no
   End 
 Go 

 Iocmd.execute("insert into mcard_list select '" + m.sample1 + "',sample2 from Table1) -not work.

 Iocmd.execute("insert into mcard_list select '" + m.sample1 + "','" + sample2 + "') - work.

I found if I separate the variable ("sample2")to be stored from another query execution and adding again back to above line without from onwards statement, it will worked fine.

Appreciate someone could help me fix these if I want to join again with "from" statement. Thanks.

Answer 1

The insert trigger is executed once per statement not once per inserted row. You have to rewrite the trigger so it handles the case where the pseudo table inserted has more than one row.

Common SQL Server Mistakes - Multi Row DML Triggers