Insert trigger not working when copying data from one table to other using insert into
Dear Sir, I have a table with insert on trigger. the trigger fires when i insert data into it. but i am trying to copy data from another table by using the insert into statement. by using insert into statement (bulk insert or copying data from one table into another) does not firing the trigger on each insert. it just fires only once for the first row insert. can you please help ?
If you make one single insert statement which results in multiple rows being inserted, your trigger will get fired only once. Therefore, like @Usman Butt says, you have to JOIN against the INSERTED virtual table instead of assigning scalar values from it. So: An UPDATE FROM is what you want to correctly update the tblStudentBasicInfo I would also advice you to not put that much logic into the trigger, since it makes troubleshooting very hard. My rules about DML-triggers are: 1. Don't use them. 2. If you must break rule #1, make sure you understand what's happening, and what the INSERTED and DELETED virtual tables contain. Also keep your trigger simple. Limit your trigger to one DML statement (update/insert/delete). 3. If you must also break rule #2, you should really know what you are doing. Use explicit transactions and TRY/CATCH inside the trigger so that the whole transaction, including the original INSERT/UPDATE/DELETE statement gets rolled back if (when) somethine goes wrong.
Ok, I beleive the following set based solution could be what you are after. Since I do not have much details, It is only guess work. Take backup, Replace all trigger code with this one, and test.(Thorough testing should be done). Hope it helps. UPDATE [dbo].[tblStudentBasicInfo] SET [Current_Balance] = CASE WHEN [current_balance_type] = 'Debit' AND i.debit > 0 THEN [Current_Balance] + i.debit WHEN [current_balance_type] = 'Credit' AND i.Credit > 0 THEN Current_Balance + i.Credit WHEN current_balance_type ='Debit' and i.Credit > 0 THEN ABS(Current_Balance - Credit) --POSITIVE VALUES ONLY WHEN current_balance_type ='Credit' and i.Debit > 0 THEN ABS(Current_Balance - i.Debit) --POSITIVE VALUES ONLY END FROM [dbo].[tblStudentBasicInfo] INNER JOIN INSERTED i ON [tblStudentBasicInfo].[Student_ID] = i.[StudentID]