代码之家 › 专栏 › 技术社区 › Jose Antonio Piedehierro Arias

PostgreSQL-插入、删除行或仅更新2列后触发

triggers function postgresql sql

0

Jose Antonio Piedehierro Arias · 技术社区 · 3 年前

主要目标是创建一个函数来更新另一个表上的3列(第一个表中的2列的更新会触发第二个表的更新)。

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

3列的名称: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;

表格:

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

0 回复 | 直到 3 年前

1

valihan ilyasov 3 年前

此触发器定义仅在UPDATE命令中将列(net_amount)和(iva_amount)指定为目标时执行函数(modify_amount):

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

如果列(net_amount)、(iva_amount)实际上更改了值,则此表单仅执行函数(modify_amount):

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

2

1

Kadet 3 年前

您可以在更新特定字段后创建触发器

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

在触发器内更新另一个表中的总和不是一个好主意(它将业务逻辑移动到触发器中,并且不保存)。

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

版本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 总和需要确保,即使发票没有头寸,金额也将计算为0。

COALESCE 哪里因为在插入操作期间没有OLD.invoice_id,并且在删除操作期间没有NEW.invoice_id。

我的假设是invoice_id不能更改。如果没有,那么将仓位从一张发票移动到另一张发票应该会更新旧发票和新发票。