如何对insert/update/delete触发器使用相同的触发器函数以避免新对象和旧对象的问题

Question

我正在寻找一种优雅的解决方案：

• 我已经创建了一个触发器函数，每当在supply上插入或更新一行时，它就会用一些细节行的总和更新表warehouse_supplies。
• PostgreSQL insert or update语法允许我为插入和更新条件共享相同的sync_supply_stock()函数。
• 但是，当我试图将after delete条件连接到函数时，它不能被重用(尽管它在逻辑上是有效的)，因为返回的对象必须是old而不是new。

-- The function I want to use for the 3 conditions (insert, update, delete)
create or replace function sync_supply_stock ()
returns trigger
as $$
begin
    -- update the supply whose stock just changed in warehouse_supply with
    -- the sum its stocks on all the warehouses.
    update supply 
    set stock = (select sum(stock) from warehouse_supplies where supply_id = new.supply_id)
    where supply_id = new.supply_id;
    
    return new;
end;
$$ language plpgsql;

-- The (probably) unnecessary copy of the previous function, this time returning old.
create or replace function sync_supply_stock2 ()
returns trigger
as $$
begin
    -- update the supply whose stock just changed in warehouse_supply with
    -- the sum its stocks on all the warehouses.
    update supply 
    set stock = (select sum(stock) from warehouse_supplies where supply_id = old.supply_id)
    where supply_id = old.supply_id;
    
    return old;
end;
$$ language plpgsql;

-- The after insert/update trigger
create trigger on_warehouse_supplies__after_upsert after insert or update
on warehouse_supplies for each row 
execute procedure sync_supply_stock ();

-- The after delete trigger
create trigger on_warehouse_supplies__after_delete after delete
on warehouse_supplies for each row 
execute procedure sync_supply_stock2 ();

我是不是遗漏了什么，还是把sync_supply_stock2()复制成了sync_supply_stock2()？

编辑

为了未来读者的利益，遵循@bergi的答案和解释，这是一个可能的分解解决方案。

create or replace function sync_supply_stock ()
returns trigger
as $$
declare
    _supply_id int;
begin
    -- read the supply_id column from `new` on insert/update conditions and from `old` on delete conditions
    _supply_id = coalesce(new.supply_id, old.supply_id);
    
    -- update the supply whose stock just changed in of_warehouse_supply with
    -- the sum its stocks on all the warehouses.
    update of_supply 
    set stock = (select sum(stock) from of_warehouse_supplies where supply_id = _supply_id)
    where supply_id = _supply_id;
            
    -- returns `new` on insert/update conditions and `old` on delete conditions 
    return coalesce(new, old);
end;
$$ language plpgsql;

create trigger on_warehouse_supplies__after_upsert after insert or update
on of_warehouse_supplies for each row 
execute procedure sync_supply_stock ();

create trigger on_warehouse_supplies__after_delete after delete
on of_warehouse_supplies for each row 
execute procedure sync_supply_stock ();

Answer 1

因为返回的对象必须是old而不是new。

不是的。返回值仅与BEFORE ROW或INSTEAD OF触发器相关。从医生们：“行级触发器触发的AFTER或语句级触发器触发的BEFORE或AFTER的返回值总是被忽略；它可能也是空的”。

因此，您只需使您的sync_supply_stock触发器函数RETURN NULL，并且它可以用于所有操作。