호출 예시(트리거 설정)

CALL PREVENT_DELETE_TRUNCATE_ON_TABLE('table_name');

CALL PREVENT_DELETE_TRUNCATE_ON_TABLE('schema_name.table_name');

트리거 해제

CALL PREVENT_DELETE_TRUNCATE_ON_TABLE('schema_name.table_name', false);

정의

--------------------------------------------------------------------------------------------
-- 지정 테이블에 대해 DELETE, TRUNCTE 실행하지 못하도록 방지
--------------------------------------------------------------------------------------------
-- [예제]
-- 설정: CALL PREVENT_DELETE_TRUNCATE_ON_TABLE('schema_name.table_name');
-- 해제: CALL PREVENT_DELETE_TRUNCATE_ON_TABLE('schema_name.table_name', false);
--------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS PREVENT_DELETE_TRUNCATE_ON_TABLE;
        CREATE PROCEDURE PREVENT_DELETE_TRUNCATE_ON_TABLE
(
    table_name TEXT,
    set_trigger BOOLEAN = true
)
AS $$
BEGIN
    -- 스키마명이 명시되지 않은 경우 public
    IF strpos(table_name, '.') = 0 THEN
        table_name := 'public.' || table_name;
    END IF;

    ----------------------------------------------
    -- CASE true: 트리거 설정
    ----------------------------------------------
    IF set_trigger = true THEN
        EXECUTE FORMAT('
CREATE OR REPLACE FUNCTION public.prevent_delete_truncate()
RETURNS TRIGGER AS $_$
BEGIN
    RAISE EXCEPTION ''해당 테이블에 DELETE와 TRUNCATE를 수행할 수 없습니다.'';
END;
$_$ LANGUAGE plpgsql;

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; $$
LANGUAGE plpgsql;