question

imrankasuri avatar image
imrankasuri asked

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 ?
triggerinsert
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
Since you can only assign one value to a variable, only one row from the inserted table is entertained. For multiple rows, you need to use JOIN instead of variables.
2 Likes 2 ·
Usman Butt avatar image Usman Butt commented ·
I can feel what is wrong with the trigger but please post the DDL of your trigger, so we could be able to make the correction. Thanks.
0 Likes 0 ·
imrankasuri avatar image imrankasuri commented ·
/****** Object: Trigger [dbo].[tblStudentLedger_afterinsert] Script Date: 04/04/2012 11:31:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[tblStudentLedger_afterinsert] on [dbo].[tblStudentLedger] AFTER insert as BEGIN SET NOCOUNT ON; Declare @Student_ID int Declare @Current_Balance numeric(18,2) Declare @Debit numeric(18,2) Declare @Credit numeric(18,2) Declare @current_balance_type varchar(6) select @Student_ID = student_id from inserted; select @Debit = debit from inserted; select @Credit = credit from inserted; select @Current_Balance = Current_Balance from tblStudentBasicInfo where Student_ID = @Student_ID; select @current_balance_type = current_balance_type from tblStudentBasicInfo where Student_ID = @Student_ID; if @current_balance_type ='Debit' and @Debit > 0 Begin update tblStudentBasicInfo set Current_Balance = @Current_Balance + @Debit where Student_ID=@Student_ID; End Else if @current_balance_type ='Credit' and @Credit > 0 Begin update tblStudentBasicInfo set Current_Balance = @Current_Balance + @Credit where Student_ID=@Student_ID; End Else if @current_balance_type ='Debit' and @Credit > 0 Begin Declare @NewBalance numeric(18,2) select @NewBalance = @Current_Balance - @Credit; if @NewBalance < 0 begin select @NewBalance= @NewBalance * -1; update tblStudentBasicInfo set Current_Balance = @NewBalance, current_balance_type='Credit' where Student_ID=@Student_ID; end else begin update tblStudentBasicInfo set Current_Balance = @NewBalance where Student_ID=@Student_ID; end End Else if @current_balance_type ='Credit' and @Debit > 0 Begin Declare @NewBalance1 numeric(18,2) select @NewBalance1 = @Current_Balance - @Debit; if @NewBalance1 < 0 begin select @NewBalance1= @NewBalance1 * -1; update tblStudentBasicInfo set Current_Balance = @NewBalance1, current_balance_type='Debit' where Student_ID=@Student_ID; end else begin update tblStudentBasicInfo set Current_Balance = @NewBalance1 where Student_ID=@Student_ID; end End END
0 Likes 0 ·
imrankasuri avatar image imrankasuri commented ·
thank you very much for reply sir. but i could not understand where to join. According to my little knowledge, when you insert a record it becomes available in INSERTED so i am getting the student_id key from the Inserted record and then updating the tblStudentBasicInfo table on behalf of this key. can you please little guide how i can do what you are suggesting? can you please modify a little code so that the trigger can run with insert into statement for multiple insertion at the same time. i will be thankful to you.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
If you make an insert of 10 rows in one insert statement, your trigger will fire once, and the INSERTED virtual table will contain 10 rows. It doesn't fire once per inserted row.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
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]
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
BTW, I have simulated it on my machine and seems like it is giving correct results. CREATE TABLE tblStudentBasicInfo ( Student_ID INT PRIMARY KEY ,Student_Name VARCHAR(50) ,Current_Balance INT ,current_balance_type VARCHAR(6) ) CREATE TABLE tblStudentLedger ( LedgerId INT IDENTITY PRIMARY KEY ,StudentId INT ,Debit DECIMAL(18, 2) ,Credit DECIMAL(18, 2) ) GO INSERT [dbo].[tblStudentBasicInfo] ( [Student_ID] ,[Student_Name] ,[Current_Balance] ,[current_balance_type] ) SELECT 1,'StudentA', 10, 'Debit' UNION ALL SELECT 2,'StudentB', 10, 'Credit' UNION ALL SELECT 3,'StudentC', 10, 'Debit' UNION ALL SELECT 4,'StudentD', 10, 'Credit' UNION ALL SELECT 5,'StudentE', 10, 'Debit' GO INSERT INTO [dbo].[tblStudentLedger] ( [StudentId] ,[Debit] ,[Credit] ) SELECT 1, 5, 0 UNION ALL SELECT 2, 0, 5 UNION ALL SELECT 3, 0, 5 UNION ALL SELECT 4, 5, 0 UNION ALL SELECT 5, 0, 5 GO SELECT * FROM [dbo].[tblStudentBasicInfo] AS TSBI GO --CLEANUP-- --DROP TABLE [dbo].[tblStudentBasicInfo], [dbo].[tblStudentLedger]
2 Likes 2 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
+1. Very high service level on that answer :)
1 Like 1 ·
imrankasuri avatar image imrankasuri commented ·
Thank you very much for your reply sir but it did not solve the issue and it is now not working even on a single insert. Let me explain in detail. i have two tables. one is students main table and second is student ledger table.
First Table have following Fields.(tblStudentBasicInfo)
StudentId int
StudentName nvarchar(50)
CurrentBalance decimal(18,2)
CurrentBalance_Type varchar(6)

Second Table Have the Following Fields
LedgerId int PK AutoNumber
StudentId int
Debit decimal(18,2)
Credit decimal(18,2)
What exactly i need, i will insert the record in Second Table (tblStudentLedger) The Record May be Like This (1,500,0). Here the trigger work starts. if i insert the amount in debit then it will updat the balance in first table and balance type will be decided according to the current balance type of the tblstudentbasicinfo table. and if i insert the amount in credit column that will also do the same and the balance type will be set accordingly.

I hope you can understand now and will provide me better solution.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
It was a guess work :) but it should have worked. Any error you are getting? One thing noticed is you have StudentId instead of Student_id. I have changed the code accordingly.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Thanks for the compliment :) :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.