postgresql 触发器插入表以更新另一个表

nc1teljy 于 2023-01-02 发布在 PostgreSQL

关注(0)|答案(1)|浏览(446)

我有两张table：

CREATE TABLE  IF NOT EXISTS  supply_history(
  id_supply int GENERATED ALWAYS AS IDENTITY,
  id_book int NOT NULL,
  title varchar(150) DEFAULT('None'),
  quantity int NOT NULL,

  PRIMARY KEY (id_supply),
  CONSTRAINT fk_id_book
      FOREIGN KEY (id_book)
          REFERENCES books(id_book)
);

以及：

CREATE TABLE  IF NOT EXISTS  quantity_books(
  id_quantity int GENERATED ALWAYS AS IDENTITY,
  id_book int NOT NULL,
  quantity int NOT NULL,

  PRIMARY KEY (id_quantity),
  CONSTRAINT fk_id_book
      FOREIGN KEY (id_book)
          REFERENCES books(id_book)
);

我想使用触发器对quantity_books和supply_history中的数量求和。quantity_books中的字段quantity包含一些整数，插入到供应表后，我想通过对两个表中的数量字段求和来更新，并将其保存在quantity_books中。
我的触发器如下所示：

-- Trigger to update quantity of books after supply
DROP FUNCTION IF EXISTS update_quantity_books_after_supply() CASCADE;
CREATE OR REPLACE FUNCTION update_quantity_books_after_supply()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $trigger$
    BEGIN
        UPDATE quantity_books AS q
            SET NEW.quantity = supply_history.quantity + OLD.quantity
            FROM supply_history
            WHERE q.id_book = supply_history.id_book;
        RETURN NEW;
    END;
$trigger$;

DROP TRIGGER IF EXISTS trigger_completion_date ON supply_history;
CREATE TRIGGER trigger_insert_supply
   AFTER INSERT
   ON supply_history
   FOR EACH ROW
       EXECUTE FUNCTION update_quantity_books_after_supply();

并通过插入触发：

CALL insert_vals_supply(5, null, 200);

问题是"设置"行，尝试了不同的组合与旧的和新的关键字，但没有工作。
多谢帮忙

postgresql

来源：https://stackoverflow.com/questions/74970806/trigger-insert-in-table-to-update-other-one

1条答案

按热度按时间

rhfm7lfc1#

您可以通过NEW访问更新后的行，因此无需再次执行update...from引用，以引用该行进入的整个表。此外，您需要处理该表上的delete、update和truncate-否则，如果您只观察insert操作，它将失去同步。
幸运的是，您可以在单个触发器中处理insert、update和delete事件：

DROP FUNCTION IF EXISTS maintain_quantity_books_trigger_function() CASCADE;
CREATE OR REPLACE FUNCTION maintain_quantity_books_trigger_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $trigger$
    BEGIN
        UPDATE quantity_books AS q
            SET quantity = q.quantity 
                           - coalesce(OLD.quantity,0)
                           + coalesce(NEW.quantity,0)
            WHERE q.id_book = coalesce(NEW.id_book,OLD.id_book);
        RETURN null; --it's an after trigger, so this value is ignored
    END;
$trigger$;

DROP TRIGGER IF EXISTS maintain_quantity_books_trigger ON supply_history;
CREATE TRIGGER maintain_quantity_books_trigger
AFTER INSERT OR UPDATE OF quantity OR DELETE ON supply_history
FOR EACH ROW EXECUTE FUNCTION maintain_quantity_books_trigger_function();

由于coalesce()以及OLD和NEW的行为或可见性，即使不检查TG_OP也是可能的：
1.对于INSERT触发器，OLD将是未定义的（空），因此它不会执行减法，而如果您正在执行DELETE或UPDATE，则不需要执行减法。
1.对于UPDATE，OLD和NEW都已定义，因此您可以减去一个并添加另一个，以反映该更新应用的校正。

DELETE不会看到NEW记录，因此它只会减去它删除的数量，而不会添加任何内容。
TRUNCATE触发器不能与其余事件一起声明为FOR EACH ROW，因此必须单独定义：

DROP FUNCTION IF EXISTS maintain_quantity_books_truncate_trigger_function() CASCADE;
CREATE OR REPLACE FUNCTION maintain_quantity_books_truncate_trigger_function() 
RETURNS TRIGGER LANGUAGE plpgsql AS $trigger$
    BEGIN
      UPDATE quantity_books AS q
      SET quantity = 0;
      RETURN NULL;
    END; 
$trigger$;

DROP TRIGGER IF EXISTS maintain_quantity_books_truncate_trigger ON supply_history;
CREATE TRIGGER maintain_quantity_books_truncate_trigger 
AFTER TRUNCATE ON supply_history
EXECUTE PROCEDURE maintain_quantity_books_truncate_trigger_function();

Online demo

赞(0）回复(0）举报 2023-01-02

我来回答

postgresql 触发器插入表以更新另一个表

1条答案

相关问题

热门标签

最新问答