24 lines
1.0 KiB
PL/PgSQL
24 lines
1.0 KiB
PL/PgSQL
-- Deferred reconciliation permits nested line creation in the same transaction
|
|
-- while preventing incomplete orders or later additions to an existing snapshot.
|
|
CREATE FUNCTION reconcile_order_lines() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE
|
|
target_order UUID;
|
|
expected NUMERIC;
|
|
actual NUMERIC;
|
|
BEGIN
|
|
IF TG_TABLE_NAME = 'orders' THEN target_order := NEW.id;
|
|
ELSE target_order := NEW.order_id;
|
|
END IF;
|
|
SELECT subtotal INTO expected FROM orders WHERE id = target_order;
|
|
SELECT COALESCE(SUM(line_total), 0) INTO actual FROM order_lines WHERE order_id = target_order;
|
|
IF expected IS DISTINCT FROM actual THEN
|
|
RAISE EXCEPTION 'Order subtotal does not match lines';
|
|
END IF;
|
|
RETURN NULL;
|
|
END;
|
|
$$;
|
|
CREATE CONSTRAINT TRIGGER orders_reconcile_lines AFTER INSERT ON orders
|
|
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION reconcile_order_lines();
|
|
CREATE CONSTRAINT TRIGGER order_lines_reconcile_total AFTER INSERT ON order_lines
|
|
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION reconcile_order_lines();
|