x

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

more ▼

asked Feb 05 '10 at 02:32 PM in Default

David Miller gravatar image

David Miller
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 05 '10 at 03:23 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

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.
Feb 05 '10 at 07:35 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x128
x114

asked: Feb 05 '10 at 02:32 PM

Seen: 1153 times

Last Updated: Feb 05 '10 at 02:32 PM