Calculation not working Trigger in SQL

Hi All,

    In My Table , I have Three Column like Qty,Price and Total, I create a Trigger for this . 

Table:

CREATE TABLE [dbo].[tbl_SO](
[SoNo] [varchar](50) NULL,
[Qty] [int] NULL,
[price] [numeric](18, 2) NULL,
[total] [numeric](18, 2) NULL
) ON [PRIMARY]


Trigger:

CREATETRIGGER trgAfterUpdateEMGSO on DB1.DBO.TBL_SO
FOR UPDATE
AS
ALTER TABLE DB2.DBO.TBL_SO DISABLE TRIGGER TrgAfterUpdateSO
UPDATE DB2.dbo.TBL_SO
SET SONO = i.SONO,
     Qty=I.QTY,
price=I.price,
total  = I.Qty*I.Price
FROM Inserted i
INNER JOIN DB2.dbo.TBL_SO tb2 ON tb2.SoNo  = i.SONo
ALTER TABLE DB2.DBO.TBL_SO ENABLE TRIGGER TrgAfterUpdateSO


Input :

1) UPDATE TBL_SO SET QTY='10',Price='100' Where SONo='10'

2)UPDATE TBL_SO SET QTY='10',Price='100'  Total=Qty*Price Where SONo='10'

Output:

What I get?

   I tried both 1 and 2, It(Query) does not work on first time,I executed (Query) second time It works.Why?

What I need?

   How to work on First Time?

Please let me know your comments and Feed back


April 20th, 2015 8:50am

2)UPDATE TBL_SO SET QTY='10',Price='100'  Total=Qty*Price Where SONo='10'

I guess you expect the the values 10 & 100 are used here to calculate the new total? No, it don't, it uses the old values, you have to use the values you pass instead =>

UPDATE TBL_SO 
SET QTY=10, Price=100, Total= 10 * 100 
Where SONo='10'

And if you have numeric values, then pass them as numeric without hyphen and not as a string with hyphen.

Free Windows Admin Tool Kit Click here and download it now
April 20th, 2015 8:58am

Hi Olaf Helper,

   Thanks for your suggestion . I tested (10*100), It works fine . But I used( Total=Qty*Price ) this way .It does not work.  

   I think, no difference between (10*100) and Qty*Price ,One difference , In First case, i put direct calculation , In Second case I put dynamic like Qty*Price.

   Finally,  we get Trigger does not support dynamic calculation. Am i right or wrong? Please guide me. 

April 23rd, 2015 5:02am

Hi Olaf Helper,

   Thanks for your suggestion . I tested (10*100), It works fine . But I used( Total=Qty*Price ) this way .It does not work.  

   I think, no difference between (10*100) and Qty*Price ,One difference , In First case, i put direct calculation , In Second case I put dynamic like Qty*Price.

   Finally,  we get Trigger does not support dynamic calculation. Am i right or wrong? Please guide me. 

Nope thats wrong

It does have a difference which is what was pointed in the last suggestion

When you do 10 * 100 the value that get stored in Total would be 1000

But when you do Qty * Price the value getting stored will depend on what was already stored in Qty and Price fields. Keep in mind that you're setting QTY=10, Price=100 in  the same update itself so it will not get affected until the update is over. So when you do in same update Qty * Price it will not be taking the same values.

Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2015 5:23am

 I think, no difference between (10*100) and Qty*Price


There is a difference, 10 & 100 represents the new value, while Qty & Price contains the old value from the table content.
April 23rd, 2015 7:56am

Hi Olaf Helper,

    Thanks for your help. I agree your points.


Free Windows Admin Tool Kit Click here and download it now
April 24th, 2015 4:24am

P.S.: Instead of calculating & storing the "total" you could create a calculated column, then you don't need to worry about updates on Qty / price:

CREATE TABLE [dbo].[tbl_SO](
	[SoNo] [varchar](50) NULL,
	[Qty] [int] NULL,
	[price] [numeric](18, 2) NULL,
	[total] AS ([Qty]*[price]) PERSISTED
) ON [PRIMARY]

April 25th, 2015 2:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics