Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

EDIT:

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;
share|improve this question
1  
What do you mean by "check total price"? Please show us your code. –  Colin 't Hart Jun 23 at 9:46
    
It sounds like you want FOR EACH STATEMENT triggers that have NEW and OLD as pseudo-tables. PostgreSQL doesn't support this (yet). There's a patch for 9.5 that might add it. –  Craig Ringer Jun 23 at 9:54
    
Why are you storing total_price_vat? This is redundant information. –  Colin 't Hart Jun 23 at 10:45
    
total_price_vat in invoice simplifies a lot of things in other parts of app. I know that it is not the cleanest thing to do but it's better than joining table invoiceitem every time I need this number. –  Don Mums Jun 23 at 11:25
    
Why not make a view? –  Colin 't Hart Jun 23 at 12:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.