在将记录插入表格后, 我有一个触发程序, 它会执行。 触发程序基本上会调用一个函数, 从插入中传递值。 但是, 虽然它有时有效, 我仍然会收到以下错误 :
Msg 8115, Level 16, State 2, Procedure UpdateNabersRating, Line 17
Arithmetic overflow error converting expression to data type int.
触发因素如下:
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
我玩了好一阵子了, 但似乎不能把手指放在这个问题上, 特别是考虑到它有时会起作用。