PostgreSQL- Function to update 3 shared columns from table B on table A-CodePudding

The main goal is to create a function that updates 3 columns on another table (the update of the 2 columns from the first table trigger the update on the second one).

CREATE TRIGGER                      trigger_modif_amount
AFTER INSERT OR DELETE OR UPDATE OF net_amount, iva_amount
ON                                  erp.tb_lines
FOR EACH ROW
EXECUTE PROCEDURE                   modif_amount();

The name of the 3 columns: net_amount , iva_amount , total_amount

    CREATE OR REPLACE FUNCTION modif_amount()
RETURNS TRIGGER AS $$
BEGIN
UPDATE erp.tb_invoice
SET (net_amount,iva_amount,tot_amount) = (select COALESCE(sum(net_amount),0),COALESCE(sum(iva_amount),0),COALESCE(sum(net_amount iva_amount),0) from tb_lines where invoice_id = coalesce(NEW.invoice_id, OLD.invoice_id))
WHERE invoice_id = coalesce(NEW.invoice_id, OLD.invoice_id);
END; 
$$ LANGUAGE plpsql;

The tables:

  CREATE TABLE erp.tb_invoice (
    co_code            CHARACTER(3) NOT NULL,
    invoice_id         INT NOT NULL,
    invoice_no         CHARACTER VARYING(15)  NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    site_id            INT NOT NULL,
    payed              CHARACTER(1) NOT NULL DEFAULT 'N',
    net_amount         REAL NOT NULL,
    iva_amount         REAL NOT NULL,
    tot_amount         REAL NOT NULL,
    last_updated_by    CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date   DATE NOT NULL,
    CONSTRAINT pk_invoice PRIMARY KEY (invoice_id),
    CONSTRAINT fk_invoice_company FOREIGN KEY (co_code) REFERENCES erp.tb_company (co_code),
    CONSTRAINT fk_invoice_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no),
    CONSTRAINT fk_invoice_site FOREIGN KEY (site_id) REFERENCES erp.tb_site (site_id)  
  );
  

  CREATE TABLE erp.tb_lines (
    invoice_id            INT NOT NULL,
    line_id               INT  NOT NULL,
    line_num              INT NOT NULL,
    item                  CHARACTER(5),
    description           CHARACTER VARYING(120)  NOT NULL,
    net_amount            REAL NOT NULL,
    iva_amount            REAL NOT NULL,
    last_updated_by       CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date      DATE NOT NULL,
    CONSTRAINT pk_lines PRIMARY KEY (line_id),
    CONSTRAINT fk_lines_invoice FOREIGN KEY (invoice_id) REFERENCES erp.tb_invoice (invoice_id)
  );

CodePudding user response：

This trigger definition to only execute the function (modif_amount) if column (net_amount) and (iva_amount) is specified as a target in the UPDATE command:

CREATE TRIGGER                trigger_modif_amount
AFTER UPDATE OF net_amount,iva_amount ON tb_lines
FOR EACH ROW
EXECUTE PROCEDURE             modif_amount();

This form only executes the function (modif_amount) if column (net_amount), (iva_amount) has in fact changed value:

CREATE TRIGGER                trigger_modif_amount
AFTER UPDATE
ON                            tb_lines
FOR EACH ROW
 WHEN ((OLD.net_amount IS DISTINCT FROM NEW.net_amount) AND 
       (OLD.iva_amount IS DISTINCT FROM NEW.iva_amount) )
EXECUTE PROCEDURE             modif_amount();

CodePudding user response：

You can create trigger which trigger after update of specific fields

https://www.postgresql.org/docs/14/sql-createtrigger.html

CREATE TRIGGER test
    AFTER INSERT OR DELETE OR UPDATE OF net_amount, iva_amount
    ON tb_lines
    FOR EACH ROW
    EXECUTE PROCEDURE modif_amount();

Updating overal sum in another table inside trigger is not a good idea (it moves business logic into triggers and is not save).

Version 1

update tb_invoice set
(net_amount,iva_amount,tot_amount) = (select COALESCE(sum(net_amount),0),COALESCE(sum(iva_amount),0),COALESCE(sum(net_amount iva_amount),0) from tb_lines where invoice_id = coalesce(NEW.invoice_id, OLD.invoice_id))
where invoice_id = coalesce(NEW.invoice_id, OLD.invoice_id);

Version 2

update tb_invoice set
(net_amount,iva_amount,tot_amount) = (select COALESCE(sum(net_amount),0),COALESCE(sum(iva_amount),0),COALESCE(sum(net_amount iva_amount),0) from tb_lines where tb_lines.invoice_id = tb_invoice.invoice_id)
where invoice_id = coalesce(NEW.invoice_id, OLD.invoice_id);

COALESCE in sum is needed to ensure, that even if invoice has no positions sums will be calculated as 0.

COALESCE in where is needed because during insert operation there is no OLD.invoice_id, and during delete operation there is no NEW.invoice_id.

My assumption is, that invoice_id cannot be changed. If not, that moving a position from one invoice to another should update ole and new one.