PostgreSQL- Trigger after insert , delete of rows OR UPDATE of only 2 columns-CodePudding

I'm trying to understand how to put in the trigger that it should work after insert or delete on a table BUT ALSO after update of ONLY 2 columns. Is that posible? I feel if I put insert,delete or update it will work after any modification of any field on that table.

The name of the columns= net_amount , iva_amount

  ------------------------------------------------------------------------------------------------
  -- Create trigger 3
  ------------------------------------------------------------------------------------------------
CREATE TRIGGER                trigger_modif_amount
AFTER UPDATE --here I need to add somehow insert,delete also
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();

The main goal is after clarifying that on the trigger side, 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).

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

 ----------------------------------------------------------------------------------------------
  -- Create procedure 3
 ----------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION modif_amount()
RETURNS TRIGGER AS $$
BEGIN
--the idea is to update these 3 columns from tb_invoice , perhaps?:
UPDATE tb_invoice
SET
iva_amount=NEW.iva_amonut
net_amount=NEW.net_amount
total_amount=NEW.total_amount
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).

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);

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.