My trigger is running forever? Any idea why?

I have created the follow TSQL trigger that appears to be running forever whenever the underlying table gets updated.

CREATE TRIGGER Trigger_MDSS_ComputeAggregates
ON dbo.MonthlyDetectionScoresSums
AFTER UPDATE, INSERT
AS
BEGIN


update dbo.MonthlyDetectionScoresSums开发者_StackOverflow
SET
YPElec = CAST(COALESCE (i.YPLocChain_TotElec, i.YPGlobChain_TotElec, i.YPSIC_TotElec) AS real),
YPGas = CAST(COALESCE (i.YPLocChain_TotGas, i.YPSIC_TotGas) AS real)
from MonthlyDetectionScoresSums mdss
inner join INSERTED i on i.ACI_OI = mdss.ACI_OI

END
GO

Do you know why it might be running for a really really long time?

May I suggest that you use computed columns and drop the trigger?

ALTER TABLE dbo.MonthlyDetectionScoresSums ADD
    YPElec AS CAST(COALESCE (YPLocChain_TotElec, YPGlobChain_TotElec, YPSIC_TotElec) AS real)
    YPGas AS CAST(COALESCE (YPLocChain_TotGas, YPSIC_TotGas) AS real)

From what I see, you are updating rows you've just updated/inserted. The DB engine will do it for you and no trigger needed.

Do you have recursive triggers turned on?

Although an infinite loop should be terminated, it's possible if your update is very large that it takes a long time to get to the nesting limit of 32:

http://msdn.microsoft.com/en-us/library/ms190739.aspx