aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_trg_itemizer_before_delete
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;