Update Trigger Always fires?

Question

Folks,

I have the following UPDTAE TRIGGER and it will ALWAYS hit my check condition (RAISERROR) that is intended to stop duplicates. Can anyone tell me what I have wrong??? I am more of an Oracle person then SQL Server...

CREATE TRIGGER TrgBlockDup_Radio_Cd ON dbo.labor FOR UPDATE AS BEGIN

    SET NOCOUNT ON; 

    DECLARE @NEW_RADIO_CD varchar(3) 

    SET @NEW_RADIO_CD = (SELECT RADIO_CD_XF FROM inserted) 

    IF @NEW_RADIO_CD IS NULL
        BEGIN
            RETURN
        END

    IF UPDATE(radio_cd_xf)
        BEGIN    
            IF EXISTS (SELECT * FROM inserted AS i INNER JOIN labor AS l ON (i.radio_cd_xf = l.radio_cd_xf AND i.radio_cd_xf = l.radio_cd_xf))
                BEGIN
                    ROLLBACK
                    RAISERROR('Attempting to update duplicate Radio/Unit Code ', 16, 1)
                END
        END
END

I hit the RAISERROR when I am updtaing a duplicate value and also when not.... It works fine on the NULL...

Thanks in advance, Miller

Answer 1

For an update trigger, you will have 1 or more rows in both deleted and inserted. The trigger fires once for all updates that happen as a result of a single statement. Also, keep in mind this trigger runs after the update happens. I think that is what is causing this problem for you.

Your first IF block will fail if you update more than 1 row at a time. In addition, it will only be true if the radio_cd_xf value is updated to null.

The second block will only execute if the radio_cd_xf value changed. The IF EXISTS check, however, is always true because the inserted data is already in the table. Also, you hav the same check twice in your join clause.

To stop duplicates, I believe you want to use an INSTEAD OF UPDATE trigger. In that case, the new values (not yet updated) will be in inserted.

Update Trigger Always fires?

Follow this question

Related Questions