CODEWITHSUNDEEP
sql-server-2008t-sqltriggers
Scott 混合理论
Scott 混合理论

Reputation: 2332

sql server UPDATE TRIGGER doesn't fire

i have a trigger like below, the logic is to change FID status after fidRule status changed.

in my app, i update 1 row in each statement, but i found sometimes(very rare) the trigger not firing.

ALTER TRIGGER [dbo].[triggerSetFIDStatus]
   ON  [dbo].[FIDRules] 
   AFTER UPDATE
AS 

BEGIN
set nocount on

DECLARE @ruleStatus INT
DECLARE @newRuleStatus INT
DECLARE @FIDAlertStatus INT

DECLARE @FIDId INT
DECLARE @isFIDEnabled int
DECLARE @ruleId INT

SELECT @ruleStatus = deleted.alertStatus,
    @FIDId = deleted.FIDID,
    @ruleId = deleted.id
from
    deleted 

SELECT @newRuleStatus = inserted.alertStatus
from
    inserted

SELECT @FIDAlertStatus = alertStatus,
       @isFIDEnabled= isEnabled
FROM FID
WHERE id = @FIDId


    IF @FIDAlertStatus <> @newRuleStatus
    BEGIN
        -- change FID-status by FIDRule-status
        UPDATE [dbo].[FID] SET alertStatus=@newRuleStatus WHERE id=@FIDId
    END

    IF @newRuleStatus >= 0 AND @newRuleStatus <> @ruleStatus
        UPDATE [dbo].[FIDRules] SET isAlertStatChanged=1, AlertStatChangeTime = SYSUTCDATETIME() WHERE id=@ruleId


END

Upvotes: 0

Views: 2828

Answers (1)

hwcverwe
hwcverwe

Reputation: 5367

The trigger will not be fired if the UPDATE statement fails or another triggers fails to execute before this trigger is fired.

One comment about your trigger itself: You are expecting one records from DELETED which is not always correct.

Please make your trigger robust enough in case DELETED contains multiple records

-- What if deleted contains multiple records?
SELECT @ruleStatus = deleted.alertStatus,
    @FIDId = deleted.FIDID,
    @ruleId = deleted.id
FROM
    deleted

You can either use SELECT TOP(1) or make sure your trigger is able to handle multiple records from the DELETED list.

Upvotes: 1

Related Questions