CODEWITHSUNDEEP
mysqltriggers
bumperbox
bumperbox

Reputation: 10214

trigger to maintain total

I have created some tables for invoicing.

There is a invoice_header and invoice_detail table. The invoice details contains in the invoice detail lines, invoice_header_id, qty, unit_price, tax_amount etc

The invoice header has the billing address, invoice no, invoice_total, etc

I have created a trigger for insert/update/delete so that the invoice_total in the header is the sum(invoice_details.qty * invoice_details.unit_price)

There is one case I am not sure how to cater for. If I update an invoice_detail line, to associate it with a different header. like this

UPDATE invoice_details SET invoice_header_id=1 WHERE invoice_header_id=2

The trigger will fire but it will update the old header record total but not the new one. How can I cater for that circumstance?

Upvotes: 2

Views: 150

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21677

Wouldn't this work for you?

From 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.)

So, you would have OLD.invoice_header_id (2) and NEW.invoice_header_id (1) Inside the trigger for you to update 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

Upvotes: 1

Related Questions