sql - PostgreSQL 触发器函数获取堆栈并且不更新表

Question

我有一个调用函数的触发器。它基本上必须在更新后更新同一张表。但它会获取堆栈并且不会更新任何内容。

这是我的触发器:

    CREATE OR REPLACE FUNCTION invtransferences_products_after() 
    RETURNS TRIGGER AS 
$BODY$
DECLARE
    TR invtransferences_products%ROWTYPE;
    v_transfer_cost NUMERIC;
BEGIN
    IF(TG_OP='INSERT') THEN
        TR := NEW;
        RAISE NOTICE 'INVTRANSFERENCE PRODUCT ADDED %',TR.id;
        UPDATE invtransferences_products  
        SET product_cost = (get_product_composition_cost(product_id, 0)*quantity )
        WHERE invtransferences_products.id=TR.id;
    ELSE
        IF (TG_OP='UPDATE') THEN
            TR := NEW;
            RAISE NOTICE 'INVTRANSFERENCE PRODUCTS UPDATED %',TR.id;
            UPDATE invtransferences_products  
            SET product_cost = (get_product_composition_cost(product_id, 0)*quantity )
            WHERE invtransferences_products.id=TR.id;
        END IF;
    END IF;
    RETURN TR;
END
$BODY$
LANGUAGE plpgsql;

这是我的表 invtransferences_products:

CREATE TABLE invtransferences_products
(
   id                  serial      NOT NULL,
   invtransference_id  bigint       NOT NULL,
   product_id          bigint       NOT NULL,
   quantity            numeric      DEFAULT 1 NOT NULL,
   created             timestamp    DEFAULT now() NOT NULL,
   modified            timestamp,
   rcv_quantity        numeric      DEFAULT 0 NOT NULL,
   pnd_quantity        numeric      DEFAULT 0 NOT NULL,
   product_cost        numeric
);

ALTER TABLE invtransferences_products
   ADD CONSTRAINT invtransferences_products_pkey
   PRIMARY KEY (id);

ALTER TABLE invtransferences_products
  ADD CONSTRAINT invtransferences_products_invtransference_id_fkey FOREIGN KEY (invtransference_id)
  REFERENCES invtransferences (id)
  ON UPDATE CASCADE
  ON DELETE CASCADE;

COMMIT;

怎么了？？请帮忙。

Answer 1

问题是触发器函数中的 UPDATE 语句导致触发器再次触发。

您应该在 NEW 中操作数据，而不是发布单独的更新。

类似于:

CREATE OR REPLACE FUNCTION invtransferences_products_after() 
    RETURNS TRIGGER AS 
$BODY$
BEGIN
    IF(TG_OP='INSERT') THEN
        RAISE NOTICE 'INVTRANSFERENCE PRODUCT ADDED %',NEW.id;
    ELSE
        IF (TG_OP='UPDATE') THEN
            RAISE NOTICE 'INVTRANSFERENCE PRODUCTS UPDATED %',NEW.id;
        END IF;
    END IF;

    NEW.product_cost := get_product_composition_cost(NEW.product_id,0)*NEW.quantity ;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

fiddle :SQLFiddle