Creating a T-SQL Trigger for Rounding Unit Price

Hello:

I created a trigger in a SQL 2008 database that I was hoping would round the unit price.  The following is my trigger:

CREATE TRIGGER [dbo].[ROUNDUNITPRICE]
ON [dbo].[SOP10200]
AFTER INSERT, UPDATE
AS
UPDATE SOP10200 SET SOP10200.UNITPRCE =
ROUND(SOP10200.UNITPRCE, 2, 1)
from SOP10200

This did not work, though.  No change took place to my unit price and, in fact, my app froze before giving me a timeout error.

Where am I going wrong with the syntax?

Thanks!  Much appreciated!

TBSupport
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>UPDATE SOP10200 SET SOP10200.UNITPRCE =
>ROUND(SOP10200.UNITPRCE, 2, 1)
>from SOP10200

(1)  The second argument is optional, and any non-zero value in it means that the result value is truncated and not rounded.  
(2)  UPDATE statements do not require a FROM unless there is two or more tables JOINed together.

So you should be good with..
UPDATE SOP10200 
SET UNITPRCE = ROUND(UNITPRCE, 2)

Open in new window

Another possibility, if the value should always be rounded, is to declare the column with a numeric data type with a scale of 2, such as numeric(5,2).
0
TBSupportAuthor Commented:
Hello:

Actually, I need that second argument because I want the truncation.  :)

In any case, I revised my trigger and got better results--sort of.  When I tab past the line containing my item, nothing happens.  But, when I click back on that line, the "correct change" happens perfectly.  There is a problem with this, though.

I obviously don't like that I have to click back on my original line for this change to happen.

Anyway, below are my revised triggers--one for the insert and one for the update.  They're both working.  But, again, I don't like having to click back on the grid.

So, these only work if I in essence "refresh" the grid line (i.e. click "Print", hit "Save", or click back on the line item).

If you can let me know what I'm doing wrong, that would be awesome!

ALTER TRIGGER dbo.[ROUNDUNITPRICE]
ON dbo.[SOP10200]
AFTER INSERT
AS
UPDATE SOP10200 SET SOP10200.UNITPRCE =
ROUND(SOP10200.UNITPRCE, 2, 1)

ALTER TRIGGER dbo.[ROUNDUNITPRICE2]
ON dbo.[SOP10200]
FOR UPDATE
AS
IF UPDATE(UNITPRCE)
BEGIN
UPDATE SOP10200 SET SOP10200.UNITPRCE =
ROUND(SOP10200.UNITPRCE, 2, 1)
END

Thanks!

TBSupport
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Forgot one very big thing:  Your trigger is currently updating all rows.
Triggers have two 'virtual' tables, inserted and deleted.
For an insert, both exist.
For an update, deleted contains the old row, and inserted contains the new (i.e. updated) row.

So to make sure only the changed row has action on it..
Declare @i int 
SELECT @i = id    -- Replace all references to ID with whatever the primary key is of the table

UPDATE SOP10200
SET UNITPRCE = i.UnitPrice
WHERE id = @id

Open in new window


>When I tab past the line containing my item, nothing happens.  
That's because no action is taken on the row until one of the columns has the focus.

>But, when I click back on that line, the "correct change" happens perfectly.
Then with the focus it is an update.

>Secondly, I also need for the price to round,
<Potentially stupid question>  would you ever need the un-rounded value, say 13.675 instead of the rounded 13.68?  Reason I ask is if the answer is yes, you may be better off saving the 13.675 in SQL and using the front-end (or a SQL view) to display it as 13.68 whenever you need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
No, there is no rounding.  I apologize, for not making that clear.

I'm afraid that I don't understand the code that you are suggesting that I use.  Primary key?  Well, I guess that would be the document number.  But, it befuddles me as to how that fits into the equation.

TBSupport
0
TBSupportAuthor Commented:
disregard.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.