question

David Miller avatar image
David Miller asked

Update Trigger Always fires?

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

updatetrigger
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
A good answer. But why not use a unique index instead of a trigger if you want to prevent duplicates? Granted the unique index will stop duplicate inserts as well as duplicates in updates, but I would think that would be desirable.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.