SQL 触发器 : row to be modified was already modified

Question

我有一个表，其中包含带有订单字段的项目，我用这些字段将它们画在树上。

CREATE TABLE items (
    menuId INTEGER,
    itemId INTEGER,
    name VARCHAR, 
    order1 INTEGER,
    order2 INTEGER,
    order3 INTEGER,
    order4 INTEGER,
);

主键是 'menuId' 和 'itemId'。

“menuId”与表“menus”的外键。

对于每个菜单，我都有一个唯一的检查，例如，对于 menu1，我不能有两个具有相同组合顺序的项目，但 menu2 可以。

我想创建一个触发器，当我插入或更新一个项目时触发，所以如果添加一个订单为 2 的新项目，订单为 2 的更新为 3，订单 3 为 4，依此类推。现在我不关心订单之间是否有空格，这是我稍后会尝试解决的问题。

触发器是这样的:

CREATE TRIGGER trg_items_menu_bibu
  BEFORE INSERT OR UPDATE
  ON items
  FOR EACH ROW
  EXECUTE PROCEDURE update_menu_order();

CREATE OR REPLACE FUNCTION update_menu_order()
  RETURNS trigger AS
$BODY$
BEGIN
    IF EXISTS (SELECT name
                FROM items
                WHERE order1 = NEW.order1
                AND order2 = NEW.order2
                AND order3 = NEW.order3
                AND order4 = NEW.order4
                AND menuId = NEW.menuId
                AND itemId <> NEW.itemId) THEN
        -- If exists an item for that menu with the same orders
        IF (NEW.order4 <> 0) THEN
            -- If it's an item in the sublevel4
            UPDATE items SET order4 = order4 + 1
                WHERE order1 = NEW.order1
                AND order2 = NEW.order2
                AND order3 = NEW.order3
                AND order4 = NEW.order4
                AND menuId = NEW.menuId
                AND itemId <> NEW.itemId;
        ELSEIF (NEW.order3 <> 0) THEN
            -- If it's an item in the sublevel3
            UPDATE items SET order3 = order3 + 1
                WHERE order1 = NEW.order1
                AND order2 = NEW.order2
                AND order3 = NEW.order3
                AND order4 = 0
                AND menuId = NEW.menuId
                AND itemId <> NEW.itemId;
        ELSEIF (NEW.order2 <> 0) THEN
            -- If it's an item in the sublevel2
            UPDATE items SET order2 = order2 + 1
                WHERE order1 = NEW.order1
                AND order2 = NEW.order2
                AND order3 = 0
                AND order4 = 0
                AND menuId = NEW.menuId
                AND itemId <> NEW.itemId;
        ELSE
            -- If it's an item in the sublevel1
            UPDATE items SET order1 = order1 + 1
                WHERE order1 = NEW.order1
                AND order2 = 0
                AND order3 = 0
                AND order4 = 0
                AND menuId = NEW.menuId
                AND itemId <> NEW.itemId;
        END IF;
    END IF;

    RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql;

如果我有这些元素:

menuId  itemId  name    order1  order2  order3  order4
1       1       menu1   1       0       0       0
1       2       menu2   1       1       0       0
1       3       menu3   1       2       0       0

然后我尝试插入以下内容:

menuId  itemId  name    order1  order2  order3  order4
1       4       menu4   1       1       0       0       

触发器正确更新为:

menuId  itemId  name    order1  order2  order3  order4
1       1       menu1   1       0       0       0
1       4       menu4   1       1       0       0       
1       2       menu2   1       2       0       0
1       3       menu3   1       3       0       0

但如果我尝试更新:

1       3       menu3   1       1       0       0

我收到一条错误消息，指出要修改的一行已被修改。

我理解问题是触发器让menu2更新order2为3，它强制menu3更新order2为4，但是menu3已经被原来的更新修改了。

我不知道如何解决这个问题，或者是否有可能。

Answer 1

对订单使用 double 而不是整数。

这样，您始终可以在两个现有商品之间插入一个新商品，只需使用订单的平均值即可。

最大的优势是无需修改任何现有条目，因此您根本不需要触发器。

这将与 ORDER BY 一起正常工作；如果出于任何原因您需要显示整数顺序，请使用 dense_rank 窗口函数在查询中生成它。