SQL) Trigger(트리거) 🔫

📌 트리거가 뭐지

특정 테이블에 INSERT, DELETE, UPDATE 같은 DML 문이 수행되었을 때 DB에서 자동으로 동작하도록 작성된 프로그램

그니깐 어떤 이벤트가 발생하면 자동으로 무언갈 수행한다는 소리인거 같다.

• 문장 트리거
  많은 행에 대한 변경 작업이 발생하더라도 오직 한번만 트리거를 발생시키는 방법

ex) UPDATE study SET grade = grade + 10 ;
이 문장이 실행됨으로 여러행이 변경될 경우 트리거는 단 한번만 실행

• 행 트리거
  조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러번 수행
  변경 후의 행은 OLD, NEW 를 통해 가져올 수 있음

🧷 트리거 형식

CREATE [OR REPLACE] TRIGGER 트리거명
	(BEFORE|AFTER) (INSERT|DELETE|UPDATE)
    ON 테이블명
    [FOR EACH ROW]
    [WHEN 조건식]
    BEGIN
END;

---

📌 트리거 사용해보기

CREATE TRIGGER `latest_activity` AFTER INSERT ON `activity` FOR EACH ROW BEGIN
    DECLARE latest TINYINT(1); - (1)

    SELECT EXISTS (
        SELECT * FROM `cow_activity` WHERE `cow_id` = NEW.cow_id AND `farm_id` = NEW.farm_id AND `type` = NEW.type AND `activity_date` > NEW.activity_date
    ) INTO `latest`; - (2)

    IF `latest` = 0 THEN
        INSERT INTO `latest_activity`(`cow_id`, `farm_id`, `activity_date`, `type`, `value`)
	    VALUES(NEW.cow_id, NEW.farm_id, NEW.activity_date, NEW.type, NEW.value) ON DUPLICATE KEY UPDATE `activity_date` = NEW.activity_date, `value` = NEW.value;
    END IF; - (3)
END;

-> 데이터가 들어왔을 때 이전 데이터의 날짜보다 이후의 날짜이면 즉, 최근 데이터이면 데이터를 다른 테이블에 저장하도록 하는 트리거

(1) TINYINT 변수 선언
(2) 최근에 들어온 데이터가 이전 데이터보다 날짜가 이전인 튜플이 있다면 latest 변수에 1(true)이 들어감 (어지간하면 있을 리 없음)
(3) 만약 false 라면 최근에 들어온 데이터를 다른 테이블에 INSERT 하도록 SQL 문을 실행

2021-10-29 추가

ON DUPLICATE KEY UPDATE `activity_date` = NEW.activity_date, `value` = NEW.value;

키가 중복될 때 키 값을 업데이트 하고 value 컬럼의 값을 insert 해주는 문장인줄 알았으나, 중복되는 키가 이미 테이블에 존재하면 value 컬럼의 값만 변경토록 하는 문장이었다.


이를 파악하지 못하고 esle 문을 사용하여 존재하는 컬럼이면 update 하도록 작성했었는데 그럴 필요가 없었던 것.


위 사실을 깨닫게 해준 Trigger 문 👇

CREATE DEFINER=`root`@`localhost` TRIGGER `log` AFTER INSERT ON `index_log` FOR EACH ROW BEGIN
	INSERT INTO `index_logs`(`idx`, `date`, `year`, `half_year`, `month`, `week`, `day`, `total`) VALUE(NEW.idx, STR_TO_DATE(CONCAT(NEW.year, '-', NEW.month, '-', NEW.day), '%Y-%m-%d'), NEW.year, IF(NEW.month < 7, 1, 2), NEW.month, NEW.week, NEW.day, 1)
	ON DUPLICATE KEY UPDATE `total` = `total` + 1;
END;