删除后使用SQL触发器以使用多行删除

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.

我有一个带有“订单”列的表,需要保持一系列连续的唯一数字。我正在尝试创建的是在删除更新“order”列的行之后触发的触发器,以便数字保持连续。

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 ;)

我知道很多人会认为“订单”列只需要连续,而不是连续,但是有很多前端JavaScript和其他SQL,用于订购/重新排序这些依赖于顺序连续的项目。我宁愿简单地让这个触发器工作而不是重写它,当然我愿意接受建议;)

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.

对于单行删除,我的触发器工作正常,但是当发生多行删除时,只有第一行被删除而其余行保持没有抛出错误。

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.

我认为问题可能是递归,因为它更新了它触发的表,但它只是一个删除触发器所以我不认为这是问题。关闭RECURSIVE_TRIGGERS并没有解决问题。

Here's the 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 > @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

1 个解决方案

#1

Rewrite your front end. Prefer to trade more development time for less runtime.

重写你的前端。希望以更少的运行时间交换更多的开发时间。

Keeping the order column contiguous by updating all out-of-order rows in the table is tremendously inefficient (remove item 1 => update 100000000 items) resulting in potentially huge update operations, is going to generate tremendous contention because updates modify a lot of rows so they content with almost any read, and ultimately, incorrect under concurrency (you'll end up with gaps and overlaps anyway). Don't do it.

通过更新表中的所有无序行保持订单列连续是非常低效的(删除项1 =>更新100000000项)导致潜在的巨大更新操作,将导致巨大的争用,因为更新修改了很多行所以他们满足于几乎所有的阅读,并最终在并发下不正确(无论如何你最终都会有差距和重叠)。不要这样做。