I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction.
CREATE CONSTRAINT TRIGGER invoice_check AFTER INSERT OR UPDATE OR DELETE ON invoice INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE invoice_total_price();
But this trigger is executed for each row. I believe it is waste of resources to check invoice for all changed row. I only need to get all the invoices which need to be checked.
I use django ORM where you cannot easily determine, which columns were changed. So column trigger is not the best thing I think.
Here is the code of invoice_total_price function:
CREATE OR REPLACE FUNCTION invoice_total_price() RETURNS TRIGGER AS $trigger_body$ DECLARE invoice RECORD; sum_prices DECIMAL; BEGIN SELECT * INTO invoice FROM invoice WHERE invoice.id = NEW.invoice_id; SELECT SUM(invoiceitem.price_vat) INTO sum_prices FROM invoiceitem WHERE invoiceitem.invoice_id = invoice.invoice_id; IF invoice.total_price_vat != sum_prices THEN RAISE EXCEPTION 'Invoice total_price_vat is not equal to sum of price_vat of its items'; END IF; RETURN NEW; END; $trigger_body$ LANGUAGE plpgsql;