Link to home
Start Free TrialLog in
Avatar of TBSupport
TBSupport

asked on

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>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).
Avatar of TBSupport
TBSupport

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
disregard.