UPDATE trigger affects all rows-CodePudding

I'm having trouble when I updated my values with a trigger.

Here is my trigger function

CREATE OR REPLACE FUNCTION insert_check_item()
    RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO invoice_item(name, quantity, item_id, total, invoice_id)
        SELECT i.name, NEW.quantity, NEW.item_id, NEW.quantity * i.price, NEW.invoice_id
        FROM item i
        WHERE new.item_id = i.item_id;


    ELSEIF TG_OP = 'UPDATE' THEN
        UPDATE invoice_item
        SET total = NEW.quantity * i.price,
            quantity = NEW.quantity
        FROM item i
        WHERE old.item_id = i.item_id;


    END IF;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_total
    BEFORE INSERT OR UPDATE
    ON invoice_item
    FOR EACH ROW
    WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE insert_check_item();

My table invoice_item depends on item to have the name and total amount

CodePudding user response：

I added this other where clause to my update and solved the problem

ELSEIF TG_OP = 'UPDATE' THEN
        UPDATE invoice_item
        SET total = NEW.quantity * i.price,
            quantity = NEW.quantity
        FROM item i
        WHERE invoice_item.item_id = i.item_id AND invoice_item.invoice_item_id = NEW.invoice_item_id;

CodePudding user response：

You are overcomplicating things. There is no need to replace the original INSERT or UPDATE operation if you just want to change the value of a column.

You can simply assign the needed value to the column in the NEW record.

CREATE OR REPLACE FUNCTION insert_check_item()
    RETURNS trigger AS
$$
DECLARE 
  l_price numeric;
BEGIN
  select i.price 
     into l_price
  from item i
  where i.item_id = new.item_id;
  
  new.total := new.quantity * l_price;
  RETURN new;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_total
    BEFORE INSERT OR UPDATE
    ON invoice_item
    FOR EACH ROW
    EXECUTE PROCEDURE insert_check_item();