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

Answer 2

Your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table Inserted might contain multiple rows.

Given that that table might contain multiple rows - which one do you expect will be selected here??

select @est_no = ins.est_no from inserted ins;

It's undefined - you might get the value from one, arbitrary row in Inserted - and all the other rows are ignored.

You need to rewrite your entire trigger with the knowledge the Deleted WILL contain multiple rows. You need to work with set-based operations - don't expect just a single row in Inserted !