CODEWITHSUNDEEP
sqlt-sqltriggerssql-server-2008-r2
user2263420
user2263420

Reputation: 1

SQL Server trigger won't run the complete update statement?

Problem: I wrote a trigger that is supposed to update the INVOICE table after an INSERT into the LINE table but it won't update the invoice table. The trigger fires but the Update statement block won't execute.

I debugged while I executed the INSERT into the line table and found out as soon as the it gets to the UPDATE statement it jumps over it and doesn't run that code. I have been trying to figure this out for a couple of days now.

Here is my trigger code

ALTER TRIGGER [dbo].[trgInvoiceInsert] ON [dbo].[LINE]
AFTER INSERT
AS
  declare @linUnits numeric(9,2);
  declare @linPrice numeric(9,2);
  declare @invNum   int;

  select @linUnits = LINE_UNITS from inserted;
  select @linPrice = LINE_PRICE from inserted;
  select @invNum = INV_NUMBER from inserted;
BEGIN
 UPDATE i --From here it jumps to the start of the next Update
    SET INV_SUBTOTAL = INV_SUBTOTAL + (@linUnits * @linPrice)
 FROM dbo.INVOICE as i
 WHERE i.INV_NUMBER = @invNum 

 UPDATE i
    SET INV_TOTAL = INV_SUBTOTAL + INV_TAX  
 FROM dbo.INVOICE as i
 WHERE i.INV_NUMBER = @invNum 


 PRINT 'Trigger fired Successfully.'

END

Upvotes: 0

Views: 730

Answers (1)

marc_s
marc_s

Reputation: 754388

Well, using a statement like this:

select @linUnits = LINE_UNITS from inserted;

indicates that you're assuming that the trigger fires per row - it does not.

SQL Server triggers are fired once per statement - so if your statement inserts multiple rows into the Line table, your trigger will fire once for the complete statement, but the Inserted pseudo table will contain multiple rows of data.

So you need to rewrite your trigger to take that into account - the Inserted table can (and will!) contain multiple rows - and your trigger code needs to deal with that.

Upvotes: 5

Related Questions