2

I have a trigger which executes after a record is inserted into a table. The trigger basically, calls a function and passes values from the insert into it. However, while it works some times, I also keep getting the following error:

Msg 8115, Level 16, State 2, Procedure UpdateNabersRating, Line 17
Arithmetic overflow error converting expression to data type int.

The trigger is as follows:

UPDATE BMS_Snapshot SET
    NABERS = dbo.SetmetricsNABERSCalculation(
        60,
        tbl.NetFloorArea,
        tbl.ElectricityCumulative,
        tbl.GasCumulative,
        tbl.ElectricityCumulativeMax,
        tbl.GasCumulativeMax,
        tbl.ElectricityMaxTotal,
        tbl.GasMaxTotal,
        tbl.NaturalGasConversionCubicMetersToMJ,
        tbl.SuburbId)
FROM
    (SELECT 
        Snap.SnapshotId AS SnapshotId,
        Snap.ElectricityCumulative AS ElectricityCumulative,
        Snap.GasCumulative AS GasCumulative,
        Building.NetFloorArea AS NetFloorArea, 
        Building.NABERSElecTotalMax AS ElectricityMaxTotal,
        Building.NABERSGasTotalMax AS GasMaxTotal,
        Building.NABERSExpiry As Expiry,
        NABERSTarget.ElecCumulativeMax AS ElectricityCumulativeMax,
        NABERSTarget.GasCumulativeMax AS GasCumulativeMax,
        [State].NaturalGasConversionCubicMetersToMJ AS NaturalGasConversionCubicMetersToMJ,
        Suburb.SuburbId AS SuburbId
     FROM inserted AS Snap 
        INNER JOIN AST_Building AS Building ON Snap.BuildingId = Building.BuildingId
        INNER JOIN ESD_Suburb AS Suburb ON Building.SuburbId = Suburb.SuburbId
        INNER JOIN ESD_State AS [State] ON Suburb.StateId = [State].StateId
        INNER JOIN AST_NABERSTarget NABERSTarget ON Snap.BuildingId = NABERSTarget.BuildingId AND
            Snap.TimeStamp = NABERSTarget.Timestamp
        /*Where Snap.SnapshotId IN (Select inserted.SnapshotId FROM inserted)*/) AS tbl
WHERE tbl.SnapshotId = BMS_Snapshot.SnapshotId AND BMS_Snapshot.Range = 1

I've been playing around with it for a while, but can't seem to put my finger on the problem, particularly given it works sometimes.

Improve this question
2
  • It is impossible to say just by looking at the query. You probably have an implicit type conversion somewhere. It could be that a parameter to the function is declared as int and you pass a varchar field as an argument that most of the times has a number in it but sometimes it has text. So you just have to check your data types. Commented May 25, 2012 at 6:16
  • Thanks Mikael, you actually helped me fix the problem. I've spent so much time looking at the query, I forgot about that. Commented May 25, 2012 at 6:26

1 Answer 1

Reset to default
2

Changed, arguments on function to FLOAT, instead of INT for two of the parameters.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.