Trigger insert not working with insert..select from statement
sql-server, sql-server-2008, t-sql, triggers, visual-foxpro
Solution
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
Problem
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.