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