plpgsql triggers

archive-requirements-history.trigger.sql

-- DROP FUNCTION public.archive_requirements_history();

CREATE OR REPLACE FUNCTION public.archive_requirements_history()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    -- Archive old row to history only if version has changed
    IF OLD.version <> NEW.version THEN
        INSERT INTO public.requirements_history (
            req_id, "text", "version", categories, created_at, updated_at
        )
        VALUES (
            NEW.req_id, NEW."text", NEW."version", NEW.categories,
            NEW.created_at, CURRENT_TIMESTAMP
        )
        ON CONFLICT (req_id, version) DO NOTHING;  -- Avoid duplicates
    END IF;

    RETURN NEW;
END;
$function$
;

saved