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.
>
>

Changing of @@IDENTITY item in a trigger

Hello!

I have a instead of insert trigger that after doing some validation, inserts a record into a specific table. Another table requires the key generated by this insert in order to add a record, which is done at the end of the trigger. When called from a client (via a recordset.update call) the newly created key from the first insert is overridden with the key from the second insert. Any ideas on how I can save the first key, then pass it back to the client? The client currently receives the second key created.

Thanks in advance,

EverettI have exactly the same problem and although I don't have a solution (yet), I have found out that this appears to be by design. ADO does a "Select @.@.IDENTITY" after an insert to retrieve the key of the record inserted (See Chapter 11 of "Programming ADO" by David Sceppa at http://www.microsoft.com/mspress/books/sampchap/3445a.asp). If there was some way to modify ADO's behaviour to use instead "Select IDENT_CURRENT('<tablename>')" then problem solved. The article describes a "Update Resync" dynamic record set property but it doesn't appear to be flexible enough to tell ADO to use IDENT_CURRENT.

The hunt continues...

Changing DB

I'm getting error message "you can't use USE statement in stored procedure
or trigger".
So the problem is using USE statement in stored procedure.
Sorry what I wasn't clear enough.
Thank you. David P.
"Steve Kass" <skass@.drew.edu> wrote in message
news:eOYNeqmyDHA.1932@.TK2MSFTNGP09.phx.gbl...

quote:

---

> David,
> You can't use a parameter after USE, but you can do something like
> this, I believe, if you know the names of all the databases that might
> be chosen:
> if @.db = 'somedatabase'
> use somedatabase
> else if @.db = 'differentdatabase'
> use differentdatabase
> else if ...
> If you wish, you can also return silently or throw an error if the value
> passed for @.db is not one you've taken care of.
> SK
>
>
> David Potahisnsky wrote:
>
database.[QUOTE]
working[QUOTE]
it[QUOTE]
doesn't[QUOTE]
>

---

Sorry - my mistake.
SK
David Potahisnsky wrote:

quote:

---

>I'm getting error message "you can't use USE statement in stored procedure
>or trigger".
>So the problem is using USE statement in stored procedure.
>Sorry what I wasn't clear enough.
>
>Thank you. David P.
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:eOYNeqmyDHA.1932@.TK2MSFTNGP09.phx.gbl...
>
>database.
>
>working
>
>it
>
>doesn't
>
>
>

---