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