title: SQL Trigger After Delete to Work With Multiple Row Delete
body: <p>I have a table with an "order" column which needs to maintain a contiguous range of unique numbers. What I'm trying to create is a trigger that fires after deleting rows which updates the "order" column so that the numbers remain contiguous.</p>
  
  <p>I know a lot of people would argue that an "order" column only needs to be continuous, not contiguous, however there is a lot of front end JavaScript, and other SQL, for ordering/reordering these items which depends on order being contiguous. I would prefer to simply get this trigger working rather than having to rewrite that, of course I'm open to suggestions ;)</p>
  
  <p>The trigger I have works fine for a single row delete, but when a multiple row delete occurs, only the first row gets deleted and the rest remain with no error thrown.</p>
  
  <p>I thought the problem may have been recursion, as it updates the table it fired from, but it's only a delete trigger so I don't think that's the problem. Turning off RECURSIVE_TRIGGERS didn't fix the issue.</p>
  
  <p>Here's the code:</p>
  
  <pre><code>CREATE TABLE [dbo].[Item]
  (
  [ItemID] INT NOT NULL IDENTITY(1, 1),
  [ItemOrder] INT NOT NULL,
  [ItemName] NVARCHAR (50)  NOT NULL
  )
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_NULLS ON
  GO
  
  CREATE TRIGGER [dbo].[trItem_odr] ON [dbo].[Item]
      AFTER DELETE
  AS
      BEGIN
          SET NOCOUNT ON ;
  
          DECLARE @MinOrder INT
  
          SELECT  @MinOrder = MIN(ItemOrder)
          FROM    DELETED
  
          DECLARE @UpdatedItems TABLE
              (
               ID INT IDENTITY(0, 1)
                      PRIMARY KEY,
               ItemID INT
              )
  
          INSERT  INTO @UpdatedItems (ItemID)
                  SELECT  ItemID
                  FROM    dbo.Item
                  WHERE   ItemOrder &gt; @MinOrder
                          AND ItemID NOT IN (SELECT   ItemID
                                             FROM     DELETED)
                  ORDER BY ItemOrder
  
  
          UPDATE  dbo.Item
          SET     ItemOrder = (SELECT ID + @MinOrder
                               FROM   @UpdatedItems
                               WHERE  ItemID = Item.ItemID)
          WHERE   ItemID IN (SELECT   ItemID
                             FROM     @UpdatedItems)
  
      END
  GO
  
  ALTER TABLE [dbo].[Item] ADD CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED  ([ItemID])
  GO
  ALTER TABLE [dbo].[Item] ADD CONSTRAINT [IX_Item_1] UNIQUE NONCLUSTERED  ([ItemName])
  GO
  CREATE UNIQUE NONCLUSTERED INDEX [IX_Item_2] ON [dbo].[Item] ([ItemOrder])
  GO
  
  INSERT INTO [dbo].[Item] ([ItemOrder], [ItemName])
  SELECT 1, N'King Size Bed' UNION ALL
  SELECT 2, N'Queen size bed' UNION ALL
  SELECT 3, N'Double Bed' UNION ALL
  SELECT 4, N'Single Bed' UNION ALL
  SELECT 5, N'Filing Cabinet' UNION ALL
  SELECT 6, N'Washing Machine' UNION ALL
  SELECT 7, N'2 Seater Couch' UNION ALL
  SELECT 8, N'3 Seater Couch' UNION ALL
  SELECT 9, N'1 Seater Couch' UNION ALL
  SELECT 10, N'Flat Screen TV' UNION ALL
  SELECT 11, N'Fridge' UNION ALL
  SELECT 12, N'Dishwasher' UNION ALL
  SELECT 13, N'4 Seater couch' UNION ALL
  SELECT 14, N'Lawn Mower' UNION ALL
  SELECT 15, N'Dining table'
  GO
  </code></pre>