MS SQL 2005 AFTER UPDATE trigger updating other row than updated

Question

Hi,

As a user with little or no experience i have to create a trigger on a table (or find another solution). What needs to be accomplished is that when the value of the column ESB is changed to '1' in another row in the table the column has to be set to '0'.

My intention was to use a AFTER UPDATE trigger, to do this.

ALTER TRIGGER [TR_PHOTO_UPD]
   ON    [SOA].[dbo].[photos_TEST]
   AFTER  UPDATE
AS 

DECLARE @ID  VARCHAR(10)
DECLARE @ESB  VARCHAR(1)

SELECT @ID  = (SELECT TOP(1) ID 
      FROM SOA.dbo.photos_TEST 
               WHERE esb = 'Q' 
               ORDER BY ARRIVALDATETIME ASC)
SELECT @ESB (SELECT esb FROM INSERTED)


IF @ESB = '1'
UPDATE SOA.dbo.photos_TEST SET esb = '0' WHERE ID = @I

This as, you probably understand, does not work, neither the next trigger definition.

ALTER TRIGGER [TR_PHOTO_UPD]
   ON    [SOA].[dbo].[photos_TEST]
   AFTER  UPDATE
AS 

DECLARE @ID  VARCHAR(10)
DECLARE @ESB  VARCHAR(1)

SELECT @ID  = (SELECT TOP(1) ID 
      FROM SOA.dbo.photos_TEST 
               WHERE esb = 'Q' 
               ORDER BY ARRIVALDATETIME ASC)
SELECT @ESB (SELECT esb FROM INSERTED)


    IF @ESB = '1'
    BEGIN
  UPDATE SOA.dbo.photos_TEST 
  SET esb = '0'
  WHERE id = (SELECT TOP(1) ID 
       FROM SOA.dbo.photos_TEST 
       WHERE esb = 'Q' 
       ORDER BY ARRIVALDATETIME ASC)
    END

After several hours of googling and trying i have not yet found out why the row is not updated to '0'. I have a suspicion that the UPDATE in an AFTER UPDATE trigger is the reason why it does not work. Has somebody any hints or better a solution ?

Cheers,

Peter

Answer 1

Both of these approaches won't work when dealing with multiple updates.

Why do you even need to do it on an case by case basis. Can't you just run an update across the table anyway.

Maybe something along the lines of:

If Update(ESB)
    begin

       Update a
       Set AnotherColumn = 0
       From YourTable a
       Join inserted ins on a.Id = ins.Id
       Where ins.ESB = 1

    end

This checks to see if it was the ESB column that was updated. If it was then it runs the update statement to set AnotherColumn to zero where the ESB value is 1

I think that should work for you

Answer 2

Thanks to the help of Barry if finalized the trigger;

    CREATE TRIGGER TR_PHOTO_AU
   ON          SOA.dbo.photos
   AFTER     UPDATE
AS 

DECLARE @MAXCONC INT  -- Maximum concurrent processes
DECLARE @CONC INT     -- Actual concurrent processes

SET @MAXCONC = 1      -- 1 concurrent process

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- If column esb is involved in the update, does not necessarily mean
-- that the column itself is updated
If ( Update(ESB) )
BEGIN
    -- If column esb has been changed to 1
    IF ((SELECT esb FROM INSERTED) = '1')
    BEGIN
        -- count the number of (imminent) active processes
        SET @CONC = (SELECT COUNT(*) 
                  FROM SOA.dbo.photos pc
                  WHERE pc.esb in ('0','R'))

        -- if maximum has not been reached
        IF NOT ( @CONC >= @MAXCONC )
        BEGIN
            -- set additional rows esb to '0' to match @MAXCONC
               UPDATE TOP(@MAXCONC-@CONC) p2
               SET p2.esb = '0'
            FROM ( SELECT TOP(@MAXCONC-@CONC) p1.esb 
                   FROM SOA.dbo.photos  p1
                   WHERE  p1.esb = 'Q'
                   ORDER BY p1.arrivaldatetime ASC 

            ) p2

        END
    END
END