aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_count_use INTEGER := 0;
begin
    -- created by T.Supol 16-May-2025
    
    -- ==========================
    -- ห้ามลบถ้า itemizer ยัง enabled
    -- ==========================
--    IF OLD.enabled THEN
--        RAISE EXCEPTION 'Cannot delete enabled itemizer with code: %', OLD.code;
--    END IF;

    -- ==========================
    -- ตรวจสอบการใช้งานใน table department (array)
    -- ==========================
    SELECT COUNT(*) INTO v_count_use
    FROM department d
    WHERE d.itemizers IS NOT NULL
      AND OLD.code = ANY(d.itemizers);

    IF COALESCE(v_count_use, 0) > 0 THEN
        RAISE EXCEPTION 'Cannot delete itemizer with code: %, because it is referenced by one or more departments.', OLD.code;
    END IF;

    -- ==========================
    -- ตรวจสอบการใช้งานใน table transactions (jsonb)
    -- ==========================
    SELECT COUNT(*) INTO v_count_use
    FROM transactions t
    JOIN LATERAL jsonb_array_elements(t.itemizers) AS elem ON t.itemizers IS NOT NULL
    WHERE elem ->> 'code' = OLD.code;

    IF COALESCE(v_count_use, 0) > 0 THEN
        RAISE EXCEPTION 'Cannot delete itemizer with code: %, because it is referenced by one or more transactions.', OLD.code;
    END IF;

    -- ==========================
    -- ผ่านทุกเงื่อนไข → อนุญาตให้ลบ
    -- ==========================
    RETURN OLD;
END;

sp_trg_itemizer_before_delete

Parameters

Definition