mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
33 lines
1.0 KiB
PL/PgSQL
33 lines
1.0 KiB
PL/PgSQL
CREATE OR REPLACE FUNCTION public.auto_owner_update_from_collection()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $function$
|
|
DECLARE
|
|
v_title TEXT;
|
|
v_content TEXT;
|
|
BEGIN
|
|
IF TG_OP = 'INSERT' THEN
|
|
v_title := 'Collection Record Created';
|
|
v_content := 'A new collection record has been created. Amount owed: $' || COALESCE(NEW.amount_owed::text, '0') || '. Status: ' || COALESCE(NEW.status, 'open') || '.';
|
|
ELSIF TG_OP = 'UPDATE' AND OLD.status IS DISTINCT FROM NEW.status THEN
|
|
v_title := 'Collection Status Changed';
|
|
v_content := 'Collection status changed from "' || COALESCE(OLD.status, 'unknown') || '" to "' || COALESCE(NEW.status, 'unknown') || '".';
|
|
ELSE
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
INSERT INTO public.owner_updates (association_id, unit_id, title, content, tags, collection_ids)
|
|
VALUES (
|
|
NEW.association_id,
|
|
NEW.unit_id,
|
|
v_title,
|
|
v_content,
|
|
'[{"name":"Collections","color":"#ef4444"}]'::jsonb,
|
|
jsonb_build_array(NEW.id::text)
|
|
);
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$function$; |