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, 2010 at 02:32 PM in Default

avatar image

David Miller
21 1 1 3

(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, 2010 at 03:23 PM

avatar image

Tom Staab ♦
14.5k 7 14 18

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, 2010 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.

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:

x161
x139

asked: Feb 05, 2010 at 02:32 PM

Seen: 1483 times

Last Updated: Feb 05, 2010 at 02:32 PM

Copyright 2016 Redgate Software. Privacy Policy