changing precision in a trigger

I am trying to change a quantity defined as float to a decimal with 2 decima
l
places.
example 25.7599999999 to 25.78. The best I get is quantity is set to 26.
Is it possiable for this trigger to fire on only a certain storeId? Thanks
for any advice!
alter TRIGGER tr_InsertUpdateTransEntry
on TransactionEntry
FOR Insert,Update
AS
declare @.Id int
declare @.Qty decimal
declare @.StoreId int
select @.Id = (select [Id] from inserted)
--select @.Qty = (select cast(quantity as decimal(10,2))from inserted)
select @.Qty = (select quantity from inserted)
select @.StoreId = (select storeId from inserted)
update transactionEntry
--set quantity = @.Qty
set quantity = cast(@.Qty as decimal(10,2))
where [Id] = @.Id
and StoreId = @.StoreId"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> I am trying to change a quantity defined as float to a decimal with 2
decimal
> places.
> example 25.7599999999 to 25.78. The best I get is quantity is set to 26.
I'm a bit -- why do you feel that 2.759 rounds to 2.78 ? Where
does the extra 0.02 come from?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||typo.. i want it to update 25.7599999 to 25.76
"Adam Machanic" wrote:

> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> decimal
>
> I'm a bit -- why do you feel that 2.759 rounds to 2.78 ? Where
> does the extra 0.02 come from?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||Three things:
1) Updating a floating point value to a rounded value won't get you much --
in a lot of cases you'll still see a non-rounded value when you query it
back again. Floating point is an inprecise datatype. Your best bets are
either to change the datatype itself to numeric or to handle the rounding at
display time.
2) Remember that triggers need to be able to handle multiple rows. This
trigger can only handle a single row as-is. What if an update affects more
than one?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> I am trying to change a quantity defined as float to a decimal with 2
decimal
> places.
> example 25.7599999999 to 25.78. The best I get is quantity is set to 26.
> Is it possiable for this trigger to fire on only a certain storeId?
Thanks
> for any advice!
> alter TRIGGER tr_InsertUpdateTransEntry
> on TransactionEntry
> FOR Insert,Update
> AS
> declare @.Id int
> declare @.Qty decimal
> declare @.StoreId int
> select @.Id = (select [Id] from inserted)
> --select @.Qty = (select cast(quantity as decimal(10,2))from inserted)
> select @.Qty = (select quantity from inserted)
> select @.StoreId = (select storeId from inserted)
> update transactionEntry
> --set quantity = @.Qty
> set quantity = cast(@.Qty as decimal(10,2))
> where [Id] = @.Id
> and StoreId = @.StoreId|||this is my first trigger, so what do i need to do to handle sets. i thought
this would fire on each insert. I think i will code around the rounding
issues in reports.
thanks Adam.
"Adam Machanic" wrote:

> Three things:
> 1) Updating a floating point value to a rounded value won't get you much -
-
> in a lot of cases you'll still see a non-rounded value when you query it
> back again. Floating point is an inprecise datatype. Your best bets are
> either to change the datatype itself to numeric or to handle the rounding
at
> display time.
> 2) Remember that triggers need to be able to handle multiple rows. This
> trigger can only handle a single row as-is. What if an update affects mor
e
> than one?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:A3BA5B6D-D179-43D0-856E-8ED506CE88FD@.microsoft.com...
> decimal
> Thanks
>
>|||It fires once per insert -- but an insert can have multiple rows; for
instance:
INSERT Tbl (Col)
SELECT 1
UNION ALL
SELECT 2
This would insert two rows into Tbl...
To handle multiple rows in a trigger, you could do something like:
UPDATE Tbl
SET Col = 'x'
WHERE EXISTS
(SELECT *
FROM INSERTED
WHERE INSERTED.PK = Tbl.PK)
-- Assuming that PK is the table's primary key
This would handle all of the rows in the INSERTED table in one shot.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:6E4A2C5F-FBD9-4472-8FF0-00FDEA88E935@.microsoft.com...
> this is my first trigger, so what do i need to do to handle sets. i
thought
> this would fire on each insert. I think i will code around the rounding
> issues in reports.
> thanks Adam.
> "Adam Machanic" wrote:
>
much --
are
rounding at
more
26.|||Thanks Adam, I will spend some time on reading up.
"Adam Machanic" wrote:

> It fires once per insert -- but an insert can have multiple rows; for
> instance:
> INSERT Tbl (Col)
> SELECT 1
> UNION ALL
> SELECT 2
> This would insert two rows into Tbl...
> To handle multiple rows in a trigger, you could do something like:
> UPDATE Tbl
> SET Col = 'x'
> WHERE EXISTS
> (SELECT *
> FROM INSERTED
> WHERE INSERTED.PK = Tbl.PK)
>
> -- Assuming that PK is the table's primary key
>
> This would handle all of the rows in the INSERTED table in one shot.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:6E4A2C5F-FBD9-4472-8FF0-00FDEA88E935@.microsoft.com...
> thought
> much --
> are
> rounding at
> more
> 26.
>
>