[MySQL] 트리거(trigger) 문장트리거, 행트리거의 차이점

조회 64회 댓글 0건

MySQL에서 트리거(trigger)는 특정 테이블에 대해 INSERT UPDATE DELETE와 같은 DML(데이터 조작 언어) 작업이 수행될 때 자동으로 실행되는 프로시저입니다.

트리거는 데이터의 무결성을 유지하거나 로깅 감사 등의 목적으로 사용될 수 있습니다. 트리거는 데이터베이스가 특정 조건에 반응하여 자동으로 특정 작업을 수행하도록 할 때 유용합니다.

▷ 트리거의 주요 특징

- 자동 실행: 트리거는 관련 테이블에 특정 DML 작업이 수행될 때 자동으로 실행됩니다.

- 데이터 무결성 보장: 데이터의 무결성을 보장하기 위해 사용될 수 있으며 복잡한 제약 조건을 구현하는 데 유용합니다.

- 감사 및 로깅: 데이터 변경 사항을 추적하여 감사 로그를 생성하는 데 사용될 수 있습니다.

▷ 트리거의 유형

MySQL에서는 크게 두 가지 유형의 트리거가 있습니다.

1. BEFORE 트리거: 특정 DML 작업이 수행되기 전에 실행됩니다. 데이터가 실제로 변경되기 전에 검증이나 수정 등의 작업을 수행할 수 있습니다.

2. AFTER 트리거: 특정 DML 작업이 수행된 후에 실행됩니다. 작업의 결과를 로깅하거나 추가적인 데이터 조작을 수행하는 데 사용될 수 있습니다.

▷ 트리거 생성 예제

  DELIMITER $$
  CREATE TRIGGER before_employee_update
  BEFORE UPDATE ON employees
  FOR EACH ROW
  BEGIN
      IF NEW.salary < 0 THEN
          SET NEW.salary = 0;
      END IF;
  END$$
  DELIMITER ;

이 트리거는 employees 테이블에 대한 UPDATE 작업이 수행되기 전에 실행됩니다. 만약 새로운 급여(NEW.salary)가 0보다 작다면 급여를 0으로 설정합니다. 이는 데이터의 무결성을 유지하는 데 도움이 됩니다.

▷ 트리거 사용 시 주의사항

- 트리거는 복잡성을 증가시킬 수 있으므로 필요한 경우에만 사용해야 합니다.

- 너무 많은 로직을 트리거 내에 포함시키면 데이터베이스 성능에 부정적인 영향을 줄 수 있습니다.

- 트리거는 데이터베이스 내부에서 자동으로 실행되므로 트리거에 의한 데이터 변경은 추적하기 어려울 수 있습니다. 따라서 감사 및 로깅 목적으로 사용할 때는 주의가 필요합니다.

MySQL에서 트리거를 사용하면 데이터 관리 작업을 자동화하고 데이터의 무결성을 보장하는 등 다양한 이점을 얻을 수 있지만 사용 시에는 성능과 유지보수 측면을 고려해야 합니다.

● 문장 트리거와 행 트리거

문장 트리거와 행 트리거는 둘 다 데이터베이스 관리 시스템(DBMS)에서 특정 조건이 만족될 때 자동으로 실행되는 코드 블록이지만 실행 시점과 적용 범위에서 차이를 보입니다.

▷ 문장 트리거(Statement Trigger)

문장 트리거는 SQL 문장이 실행될 때 한 번만 실행됩니다. 이는 트리거가 발동된 SQL 문장에 의해 영향을 받는 모든 행에 대해 단 한 번만 실행되며 개별 행의 변경 사항에 따라 반복적으로 실행되지 않습니다. 문장 트리거는 주로 문장 수행 전후의 상태를 확인하거나 로깅 감사 등의 작업에 사용됩니다.

예시: 테이블에 데이터를 삽입하는 SQL 문장이 실행될 때 삽입 작업 전후에 로그를 남기는 문장 트리거를 설정할 수 있습니다. 이 트리거는 삽입 작업이 수행될 때 단 한 번만 실행되며 몇 개의 행이 삽입되든 상관없이 로그는 한 번만 남게 됩니다.

▷ 행 트리거(Row Trigger)

행 트리거는 SQL 문장에 의해 영향을 받는 각 행에 대해 개별적으로 실행됩니다. 즉 한 SQL 문장이 여러 행에 영향을 미치는 경우 해당 행마다 트리거가 실행됩니다. 행 트리거는 주로 데이터의 무결성을 유지하거나 개별 행의 데이터 변화에 대해 세밀한 조정을 필요로 할 때 사용됩니다.

예시: 특정 테이블의 행이 업데이트될 때마다 해당 행의 변경 사항을 별도의 테이블에 기록하는 행 트리거를 설정할 수 있습니다. 만약 한 번의 업데이트 작업으로 10개의 행이 변경되면 트리거는 10번 실행되어 각 행의 변경 사항을 개별적으로 처리합니다.

▷ 차이점 요약

- 실행 빈도: 문장 트리거는 한 SQL 문장에 대해 한 번만 실행되는 반면 행 트리거는 영향을 받는 각 행마다 실행됩니다.

- 적용 범위: 문장 트리거는 전체 SQL 문장의 실행에 대한 반응으로 동작하고 행 트리거는 개별 행의 변경에 대해 반응합니다.

- 사용 목적: 문장 트리거는 주로 전체 작업의 감사 및 로깅에 사용되고 행 트리거는 데이터의 무결성 유지 및 세밀한 데이터 관리에 사용됩니다.

UPDATE tblTest SET tt1 WHERE doc_id IN (1,3,5,7,8) 같은 SQL 문장이 실행될 때

만약 tblTest 테이블에 doc_id가 1, 3, 5, 7, 8인 행이 모두 존재한다면

- 문장트리거난 1회 실행

- 행트리거는 5회 실행

● 아래는 리뷰에 대한 행트리거 예제

  # 트리거 삭제
  # DROP TRIGGER tr_insert_review ;
  # DROP TRIGGER tr_delete_review ;
  # DROP TRIGGER tr_update_review ;

  # 입력
  DELIMITER $$
  CREATE DEFINER=`root`@`localhost` TRIGGER tr_insert_review AFTER INSERT ON review.review_data
  FOR EACH ROW BEGIN
      DELETE FROM app.review_temp WHERE doc_id=NEW.review_id;
      INSERT INTO app.review_temp SELECT NEW.review_id, NOW(), 'insert' FROM review.review_data WHERE review_key=NEW.review_key;
  END $$
  DELIMITER ;

  # 삭제
  DELIMITER $$
  CREATE DEFINER=`root`@`localhost` TRIGGER tr_delete_review AFTER DELETE ON review.review_data
  FOR EACH ROW BEGIN
      REPLACE INTO app.review_temp VALUES (OLD.review_id, NOW(), 'delete');
  END $$
  DELIMITER ;

  # 변경
  DELIMITER $$
  CREATE DEFINER=`root`@`localhost` TRIGGER tr_update_review AFTER UPDATE ON review.review_data
  FOR EACH ROW BEGIN
      UPDATE app.review_temp SET data_date=NOW(), status_name='update' WHERE doc_id=NEW.review_id;
  END $$
  DELIMITER ;

  # 현재 트리거 리스트 확인
  SHOW TRIGGERS \G


  # 입력
  INSERT INTO review.review_data (review_key,review_id,review_used) VALUES (1,1,'n');
  SELECT * FROM review.review_data WHERE review_key=1;
  SELECT * FROM app.review_temp WHERE doc_id=21;

  # 업데이트
  UPDATE review.review_data SET review_score=1 WHERE review_key=1;
  SELECT * FROM review.review_data WHERE review_key=1;
  SELECT * FROM app.review_temp WHERE doc_id=21;

  # 삭제
  DELETE FROM review.review_data WHERE review_key=1;
  SELECT * FROM review.review_data WHERE review_key=1;
  SELECT * FROM app.review_temp WHERE doc_id=21;
  