호출 예시(트리거 설정)

CALL PREVENT_DELETE_TRUNCATE_ON_SCHEMA('schema_name');

트리거 해제

CALL PREVENT_DELETE_TRUNCATE_ON_SCHEMA('schema_name', false);

정의

--------------------------------------------------------------------------------------------
-- 지정 스키마 내 모든 테이블에 대해 DELETE, TRUNCTE 실행하지 못하도록 방지
--------------------------------------------------------------------------------------------
-- [예제]
-- 설정: CALL PREVENT_DELETE_TRUNCATE_ON_SCHEMA('schema_name');
-- 해제: CALL PREVENT_DELETE_TRUNCATE_ON_SCHEMA('schema_name', false);
--------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS PREVENT_DELETE_TRUNCATE_ON_SCHEMA;
        CREATE PROCEDURE PREVENT_DELETE_TRUNCATE_ON_SCHEMA
(
    schema_name TEXT,
    set_trigger BOOLEAN = true
)
AS $$
DECLARE
    table_record RECORD;
    table_name TEXT;
BEGIN
    EXECUTE('
CREATE OR REPLACE FUNCTION public.prevent_delete_truncate()
RETURNS TRIGGER AS $_$
BEGIN
    RAISE EXCEPTION ''해당 테이블에 DELETE와 TRUNCATE를 수행할 수 없습니다.'';
END;
$_$ LANGUAGE plpgsql;
    ');

    FOR table_record IN
        SELECT c.relname AS table_name
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
         WHERE n.nspname = schema_name
           AND c.relkind = 'r'  -- 일반 테이블
         ORDER BY 1 ASC
    LOOP
        table_name := table_record.table_name;

        ----------------------------------------------
        -- CASE true: 트리거 설정
        ----------------------------------------------
        IF set_trigger = true THEN
            EXECUTE FORMAT('
CREATE TRIGGER prevent_truncate_delete
BEFORE DELETE OR TRUNCATE ON %s
FOR EACH STATEMENT EXECUTE FUNCTION public.prevent_delete_truncate();
            ', table_name);
        ----------------------------------------------
        -- CASE false: 트리거 해제
        ----------------------------------------------
        ELSE
            EXECUTE FORMAT('DROP TRIGGER IF EXISTS prevent_truncate_delete ON %s', table_name);
        END IF;
    END LOOP;
    
END; $$
LANGUAGE plpgsql;