mysql - 触发维持总

Question

我已经创建了一些用于开发票的表格。

有一个 invoice_header 和 invoice_detail 表。发票明细包含在发票明细行、invoice_header_id、qty、unit_price、tax_amount等

发票抬头有账单地址、发票号、invoice_total等

我已经为插入/更新/删除创建了一个触发器，以便标题中的 invoice_total 是总和(invoice_details.qty * invoice_details.unit_price)

有一种情况我不确定如何处理。如果我更新 invoice_detail 行，将其与不同的标题相关联。像这样

UPDATE invoice_details SET invoice_header_id=1 WHERE invoice_header_id=2

触发器将触发，但它会更新旧的标题记录总数而不是新的。我该如何应对这种情况？

Answer 1

这不适合你吗？

来自 Mysql trigger syntax

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

因此，您将在触发器中拥有 OLD.invoice_header_id (2) 和 NEW.invoice_header_id (1) 以供您更新 invoice_header

CREATE TRIGGER check BEFORE UPDATE ON invoice_detail
FOR EACH ROW
BEGIN
    IF NEW.invoice_header_id <> OLD.invoice_header_id THEN
        You Do the math and update both invoice_header lines
    END IF;
END