CODEWITHSUNDEEP
sql-servervisual-studio
Olivier Bouchoms
Olivier Bouchoms

Reputation: 15

Decimal automatically rounds

In my SQL Server database in Visual Studio, I have a trigger trgAfterUpdateAndInsertTeam, which is executed when I insert a new row into my Team table:

CREATE TRIGGER [dbo].[trgAfterUpdateAndInsertTeam]
ON Team
FOR INSERT
AS
    DECLARE @scoreperturn DECIMAL(6,3) = 0,
            @winlossratio DECIMAL(6,3) = 0,
            @idTeam INT = 0;

BEGIN
    /* Get data */
    SELECT
        @scoreperturn = [Score] / [Turns], 
        @winlossratio = [Wins] / [Losses], 
        @idTeam = [idTeam]
    FROM
        inserted

    /* Update data */
    UPDATE TEAM
    SET [ScorePerTurn] = ROUND(@scoreperturn, 3),
        [WinLossRatio] = ROUND(@winlossratio, 3)
    WHERE [idTeam] = @idTeam
END

The output in the table is a decimal number, the column is also a decimal. But it is rounded automatically. So when I should have an output like 3.421 it becomes 3.000 in the table. I tried with and without the ROUND() function but that makes no difference. What should I do?

Upvotes: 0

Views: 85

Answers (1)

Thom A
Thom A

Reputation: 95906

@marc_s is completely right, this trigger is going to cause you problems. if you INSERT 2 (or more) rows into your table, then only the team that filled the variables will be updated.

You would be much better off using a Calculated column and binning your trigger. Something like:

ALTER TABLE Team DROP COLUMN ScorePerTurn; --You'll need to drop the column first
ALTER TABLE Test ADD ScorePerTurn AS CONVERT(Decimal(6,3),CONVERT(Decimal(6,3),[Score]) / [Turns]); --Then add it again as a Computer Column.
--Then do the same for the other column
ALTER TABLE Team DROP COLUMN WinLossRatio;
ALTER TABLE Test ADD WinLossRatio AS CONVERT(Decimal(6,3),CONVERT(Decimal(6,3),[Wins]) / [Losses]);

And don't forget to DROP your trigger.

Upvotes: 2

Related Questions