My trigger is not updating properly the tables in database-CodePudding

I am very new to triggers and cannot do this thing, which in fact I believe is really simple in concept. For context, I am working on this database which has several tables. Two of them, tb_invoice and tb_lines, have, respectively, invoices and lines from these invoices (for example, if an invoice with invoice_id = 1 has 3 lines, these 3 lines are in tb_lines with IDs 1,2 and 3 (also with invoice_id equal to 1). Every line (in tb_lines) and every invoice (in tb_invoice) has a price attribute.

What I want to do is: when a user changes any price on the tb_lines database, the increment on its invoice price (positive or negative) needs to change in that same amount.

Let me try to illustrate it with a more feasible example: if there is an invoice with lines apple (2€) and orange (1€), that invoice's price would be 3. If I change, from the lines table, the apple price to 3€, I would like the trigger to automatically change that particular invoice price to 4€ (as the total amount for the invoice has increased in 1€).

I attach the code that I was doing but just will not work:

NOTE: erp is the database schema that all tables are located in.


DECLARE net_increment INTEGER;
DECLARE invoice_no INTEGER;

CREATE FUNCTION update_amounts() RETURNS TRIGGER
    LANGUAGE 'plpgsql'
    AS $$
BEGIN
--If the row's price has changed: 
    IF NEW.net_amount != OLD.net_amount
        --Store the increment in the incremento_neto variable.
        incremento_neto := NEW.net_amount - OLD.net_amount
        --Then update the price of that particular invoice in that same amount.
        UPDATE erp.tb_invoice
            SET net_amount = net_amount   net_increment
        WHERE invoice_id = invoice_no;
        RETURN NEW; --(I really do not know if this is needed, as I am updating another table.)
    END IF;
END;
$$;

--Create the trigger that executes the just created function.
CREATE TRIGGER last_mod_trig
  AFTER UPDATE ON erp.tb_lines
  FOR EACH ROW
  EXECUTE PROCEDURE update_amounts();

Also, notice that I also do not know if the variable declaration needs to be inside the function or not, I tried both but neither of them worked :(

Thanks in advance!

CodePudding user response：

The problem is the line

WHERE invoice_id = invoice_no

from

UPDATE erp.tb_invoice
    SET net_amount = net_amount   net_increment
WHERE invoice_id = invoice_no;

You would like to compare the invoice_id from the table erp.tb_invoice to the invoice_no of the row in tb_lines, not the invoice_no of tb_invoice. The new version of the row that triggered the trigger is stored in NEW, so that should read

UPDATE erp.tb_invoice
    SET net_amount = net_amount   net_increment
WHERE invoice_id = NEW.invoice_no;

Since you asked: the return value of the trigger function is used as input for the next trigger function or as the row that is actually inserted (in BEFORE triggers). If this is the only AFTER trigger on that table, you might as well RETURN NULL;.