Trigger Multiplying Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger Multiplying Values

Author

Topic

cabradley
Starting Member

5 Posts

Posted - 2009-10-27 : 05:54:45

Hi,
I have a trigger which when a row is updated should take the fee and the exchangerate and multiple them together to get the GBPfee.

declare @id int
declare @fee decimal
declare @ExchangeRate decimal
declare @GBPFee decimal

set @gatewayid = (SELECT id FROM inserted)
set @fee = (SELECT fee FROM inserted)
set @ExchangeRate = (SELECT exchangerate FROM inserted)
set @GBPFee = @fee * @exchangerate

update [tblGatewayStage]
set GBPFee = @GBPFee where id= @id

The problem is that the multiplying seems to be rounding up the exchange rate (i.e. from 1.655 to 2) so the GBPFee is then incorrect.

Any ideas how to fix this?

Thanks :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-27 : 06:05:44

You need to specify the length for decimal datatype

ex

decimal(12,2)

Madhivanan

Failing to plan is Planning to fail

cabradley
Starting Member

5 Posts

Posted - 2009-10-27 : 06:38:46

thanks very much that worked perfectly

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-27 : 06:41:41

your trigger query can only handle situation where only 1 record is being updated or inserted. You need to handle cases when multiple record are being updated / inserted at one time.


update t
set    GBPFee = i.fee * i.exchangerate 
from   [tblGatewayStage] t
       inner join inserted i on t.id = i.id

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources