trigger.sql

DROP FUNCTION IF EXISTS avoid_quota_beyond_limit() CASCADE;
CREATE OR REPLACE FUNCTION avoid_quota_beyond_limit() RETURNS TRIGGER AS $$
    DECLARE
        var_group_id uuid;
        var_product_id uuid;
        var_quota_by_group int;
        var_total_quotas int ;
        var_current_quotas int;
    BEGIN
        select product_id into var_product_id from groups where id = NEW.group_id;
        select coalesce(sum(quotas), 0) into var_total_quotas from user_groups where group_id = NEW.group_id;
        select quotas_by_group into var_quota_by_group from products where id = var_product_id;

        RAISE NOTICE 'GROUP TO INSERT ID: %', NEW.group_id;
        RAISE NOTICE 'TOTAL_QUOTAS: %', var_total_quotas;
        RAISE NOTICE 'QUOTAS TO INSERT: %', NEW.quotas;

        var_current_quotas := var_total_quotas + NEW.quotas;

        IF var_current_quotas > var_quota_by_group THEN 
            RAISE EXCEPTION 'CURRENT QUOTA VALUE IS EXCEEDING THE CURRENT QUOTA (%)', ('MAX_QUOTAS (' || var_current_quotas || ') - TO_INSERT (' ||  NEW.quotas || ')');
        END IF;

        return NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER avoid_quota_beyond_limit
BEFORE INSERT ON user_groups
    FOR EACH ROW EXECUTE PROCEDURE avoid_quota_beyond_limit();