[ DataBase / SQL ] 트리거(Trigger)와 프로시저(Procedure) 를 알아보자

SQL

[ DataBase / SQL ] 트리거(Trigger)와 프로시저(Procedure) 를 알아보자

menus 2024. 11. 27. 14:35

728x90

안녕하세요 ! (●'◡'●)

이번 글에서는 SQL의 프로시저와 트리거를 공부한 내용을 정리하려고 합니다!!

「 트리거와 프로시저의 정의 및 이해 」

트리거 (Trigger)

트리거는 어떤 트랜잭션이 일어날 때 자동으로 반응하여 다른 명령을 실행하는 기능입니다.

이는 테이블에 대한 이벤트에 반응하여 자동으로 실행되는 코드로, 주로 DDL, DML 또는 특정 DB 작업(LOGOFF, SHUTDOWN 등)에 반응하여 실행됩니다.

예를 들어, 특정 테이블에 행을 삽입하거나 변경/삭제할 때 트리거가 활성화되며, 그에 맞는 SQL문이 자동으로 실행됩니다.

장점

데이터 무결성 강화: 트리거는 자동으로 실행되므로 데이터의 일관성과 무결성을 유지하는 데 유리합니다.
검사 기능 확장: 특정 작업이 수행되기 전에 추가적인 검사를 자동으로 실행할 수 있어 유용합니다.
사용자 편의성 제공: 트리거를 사용하면 특정 작업을 자동화하여 사용자 경험을 개선할 수 있습니다.
효과적인 데이터 보관: 예를 들어, 데이터가 변경될 때마다 로그를 남기거나 기록을 자동으로 보관할 수 있습니다.

단점

유지보수 어려움: 트리거의 동작이 자동으로 실행되기 때문에, 문서화가 부족하면 중간에 투입된 개발자들이 트리거의 동작을 파악하기 어려울 수 있습니다.
과도한 사용 시 복잡성 증가: 여러 트리거가 서로 의존적이게 되어 관리가 복잡해질 수 있습니다. 하나의 트리거가 실행되면, 그 결과로 다른 트리거가 활성화되는 순차적 실행이 반복될 수 있습니다.
대규모 DB에서의 관리 어려움: 여러 트리거가 상호작용을 일으킬 때, 시스템이 복잡해지고 관리가 어려워질 수 있습니다.

구성

CREATE TRIGGER <트리거명>
[BEFORE | AFTER] <이벤트>
ON <테이블명>
[FOR EACH ROW]

BEGIN
<트리거 실행 코드> -- SQL 문
[EXCEPTION]
<예외 처리 코드> -- 예외 처리 (선택적)

END;

예시를 보면서 이해해볼까요?

-- 직원 퇴사 시 급여 테이블에 퇴사 기록을 추가하는 트리거 정의
CREATE TRIGGER after_employee_resignation
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 퇴사일이 변경되면 (즉, 퇴사 처리 시)
    IF OLD.fire_date IS NULL AND NEW.fire_date IS NOT NULL THEN
        DECLARE current_salary DECIMAL(10, 2);  -- 급여 정보 저장

        -- 해당 직원의 급여 정보 조회
        SELECT salary INTO current_salary
        FROM salaries
        WHERE employee_id = NEW.employee_id
        AND effective_date = (SELECT MAX(effective_date) FROM salaries WHERE employee_id = NEW.employee_id);

        -- 급여 테이블에 퇴사 기록 추가
        INSERT INTO salaries (employee_id, salary, effective_date)
        VALUES (NEW.employee_id, current_salary, NEW.fire_date);

        -- 퇴사 처리 완료 메시지 출력 (옵션)
        SELECT CONCAT('Employee ID: ', NEW.employee_id, ' - Resignation recorded with salary update.');
    END IF;
END;

이 트리거는 퇴사자 발생 시, employees 테이블에 퇴사일이 입력되면 salary 테이블에 해당 직원의 퇴사 기록을 추가합니다.

프로시저 (Stored Procedure)

프로시저는 일련의 SQL 명령을 미리 저장해놓고, 필요할 때 한 번의 호출로 실행할 수 있는 SQL 명령 집합입니다. SQL 명령뿐만 아니라 IF, WHILE 등의 제어문을 포함할 수 있어 복잡한 작업을 절차적으로 처리할 수 있습니다. 즉, 일종의 프로그램처럼 동작하며, 특정 작업을 반복적으로 실행할 수 있도록 만들어집니다.

장점

여러 SQL문을 하나의 요청으로 실행: 네트워크 부하를 줄이고, 여러 쿼리를 한 번에 처리할 수 있어 성능이 개선됩니다.
절차적 기능 구현: 프로시저는 제어문(IF, WHILE 등)을 포함할 수 있어 SQL만으로는 할 수 없는 절차적인 기능을 구현할 수 있습니다.
개발 업무 구분: DB 개발자와 애플리케이션 개발자가 역할을 구분할 수 있게 도와줍니다. 프로시저 수정은 DB 개발자가 담당하므로 관리가 용이합니다.
기능 변경이 용이: 기능이 변경될 때, 프로시저만 수정하면 되므로 효율적입니다. 서버 재기동 없이 수정이 가능해 관리가 용이합니다.

단점

문자나 숫자열 연산 성능 저하: 복잡한 연산이나 대량의 데이터를 처리할 때, 프로시저는 일반적인 C나 Java 코드에 비해 성능이 떨어질 수 있습니다.
유지보수 어려움: 프로시저의 동작을 문서화하지 않으면, 어디서 어떤 프로시저가 호출되는지 파악하기 어려워 관리가 어려워집니다.

구성

CREATE PROCEDURE <프로시저명> (
[입력 파라미터]
)

IS
BEGIN
-- 프로시저 내 SQL 문
<SQL 문들>
[EXCEPTION]
<예외 처리 코드> -- 예외 처리 (선택적)

END <프로시저명>;

예시를 보면서 이해해볼까요?

-- 퇴사 처리 프로시저 예시
CREATE PROCEDURE process_employee_resignation(
    IN emp_id INT,                     -- 직원 ID
    IN resignation_date DATE            -- 퇴사일
)
IS
BEGIN
    DECLARE current_salary DECIMAL(10, 2);  -- 직원의 현재 급여 저장

    -- 퇴사일을 직원의 fire_date에 반영
    UPDATE employees
    SET fire_date = resignation_date
    WHERE employee_id = emp_id;

    -- 해당 직원의 급여 정보 조회
    SELECT salary INTO current_salary
    FROM salaries
    WHERE employee_id = emp_id
    AND effective_date = (SELECT MAX(effective_date) FROM salaries WHERE employee_id = emp_id);

    -- 급여 테이블에 퇴사 기록 추가
    INSERT INTO salaries (employee_id, salary, effective_date)
    VALUES (emp_id, current_salary, resignation_date);

    -- 퇴사 처리 완료 메시지 출력
    SELECT CONCAT('Employee ID: ', emp_id, ' has been resigned on ', resignation_date, '. Salary updated.');
END process_employee_resignation;

-- 프로시저 사용 예시
-- 직원 ID가 102번인 직원의 퇴사를 2024년 11월 30일로 처리
CALL process_employee_resignation(102, '2024-11-30');

이 프로시저는 직원 ID와 퇴사일을 매개변수로 받아서 해당 직원의 퇴사일 반영, 현재 급여를 적용하는 작업을 수행합니다.

트리거 / 프로시저의 차이점

	TRIGGER	STORED PROCEDURE
호출 시점	특정 이벤트가 발생할 때 자동 실행	명시적으로 호출 시 실행
동작	특정 테이블의 행 삽입/수정/삭제 등의 이벤트에 반응	하나 이상의 SQL 명령을 절차적으로 실행
내부 정의	트리거 내부에서 프로시저 정의 가능	프로시저 내부에서 트리거 정의 불가
매개변수	매개변수나 값을 반환할 수 없음	매개변수와 값을 반환할 수 있음
관리 용이성	과도한 사용 시 관리 어려움	비교적 관리가 용이하나 문서화 필요
용도	자동화된 작업, 데이터 무결성 유지	반복적인 작업, 복잡한 절차적 처리

※ 트리거는 자동으로 실행, 프로시저는 사용자 호출에 의해 수동적으로 실행된다는 차이가 주요 차이점이라고 볼 수 있습니다.

트리거와 프로시저를 알고 적절한 경우에 사용하면 성능 개선에 도움을 줄 수 있습니다.

트리거는 자동화된 작업을 통해 데이터 무결성을 유지하고, 프로시저는 복잡한 작업을 절차적으로 처리하며 성능을 개선합니다. 각각의 장점과 단점이 있기 때문에, 시스템의 요구사항에 맞는 도구를 선택하여 사용하는 것이 중요합니다.

< 트리거 - 이벤트 기반 자동화 >

< 프로시저 - 복잡한 작업을 효율적으로 처리 >

오늘은 이렇게 데이터베이스에서 성능과 효율적인 처리를 위해 사용되는 트리거와 프로시저를 정리해보았습니다!

다음 글에서 더 유용한 내용으로 찾아오겠습니다!!

화이팅