The PostgreSQL trigger function gets the stack and does not update the table

advertisements

I have a trigger that calls a function. It basically must update the same table after update. But it get stacks and does not update anything.

This is my trigger:

    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;

This is my table 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;

What's wrong?? Help please.


The problem is that the UPDATE statement in the trigger function causes the trigger to fire again.

Instead of issuing a separate update, you should manipulate the data in NEW.

Something like:

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 at: SQLFiddle