27 lines
1.1 KiB
PL/PgSQL
27 lines
1.1 KiB
PL/PgSQL
-- Change invoice_revenues uniqueness from global to per-period
|
|
-- A revenue can be on multiple invoices, just not on invoices with overlapping periods
|
|
|
|
-- Replace the trigger function to check for overlapping periods instead of global uniqueness
|
|
CREATE OR REPLACE FUNCTION check_invoice_revenue_unique()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Check if this revenue is already on an invoice with an overlapping period
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM invoice_revenues ir
|
|
JOIN invoices existing ON existing.id = ir.invoice_id
|
|
JOIN invoices new_inv ON new_inv.id = NEW.invoice_id
|
|
WHERE ir.revenue_id = NEW.revenue_id
|
|
AND ir.id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000'::uuid)
|
|
AND existing.start_date <= new_inv.end_date
|
|
AND existing.end_date >= new_inv.start_date
|
|
) THEN
|
|
RAISE EXCEPTION 'Revenue % is already on an invoice with an overlapping period', NEW.revenue_id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- The trigger already exists, so no need to recreate it
|
|
-- It will use the updated function automatically
|