A SQL trigger is a database function that PostgreSQL executes automatically after specified data changes or database events. It keeps derived data synchronized without requiring every application path to recalculate it manually. section 2.6.2
Keep a pending order total current
When Bob adds items to his cart, an order initially has no total. A trigger on sales.order_items recalculates the pending order total after every insert, update, or delete. Adding two units at $16.54 produces a total of $33.08; adding four produces $66.16. section 2.6.2
Listing 2.25 Trigger function that updates the order’s total amount
CREATE OR REPLACE FUNCTION sales.update_order_total()
RETURNS TRIGGER AS $$
BEGIN
UPDATE sales.orders
SET total_amount = (
SELECT COALESCE(SUM(oi.quantity * oi.price), 0)
FROM sales.order_items oi
WHERE oi.order_id = COALESCE(NEW.order_id, OLD.order_id)
)
WHERE id = COALESCE(NEW.order_id, OLD.order_id) AND status = 'pending';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
The trigger function sums order-item quantities and prices, uses NEW or OLD to identify the affected order, and updates only pending orders. section 2.6.2
Listing 2.26 Trigger that updates the order’s total amount
CREATE TRIGGER trigger_update_order_total AFTER INSERT OR UPDATE OR DELETE ON sales.order_items FOR EACH ROW EXECUTE FUNCTION sales.update_order_total();
The trigger binds the function to sales.order_items for each row after INSERT, UPDATE, or DELETE. section 2.6.2
