SQL - Trigger

MySQL 트리거란 무엇인가?

MySQL 트리거는 테이블에 연결된 저장 프로그래밍의 일종입니다. 특정 이벤트가 발생하면 자동으로 활성화 됩니다. 이 이벤트는 반드시 INSERT, UPDATE, 또는 DELETE와 같은 DML(데이터 조작 언어) 명령어와 관련이 있어야 합니다. 트리거는 데이터베이스의 일관성과 무결성을 유지하는데 매우 유용한 도구입니다.

 

트리거는 두 가지 유형으로 나뉩니다.

• BEFORE 트리거: 이벤트 발생 전에 실행 됩니다.
• AFTER 트리거:  이벤트 발생 후에 실행 됩니다.

예를 들어, 트리거는 테이블에 새 레코드가 삽입되기 전에 또는 레코드가 업데이트 된 후에 활성화 될 수 있습니다.

DELIMITER $$

CREATE TRIGGER before_salaries_insert
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN 
	IF NEW.salary < 0 THEN 
		SET NEW.salary = 0; 
	END IF; 
END $$

DELIMITER ;

INSERT INTO salaries VALUES ('10001', -92891, '2010-06-22', '9999-01-01');
SELECT * FROM salaries WHERE emp_no = '10001';

이 트리거는 salaries 테이블에 새로운 데이터가 삽입 될 때 삽입 되는 salary 값이 음수이면 자동으로 0으로 설정하는 트리거 입니다. NEW.salary는 삽입되는 새 행의 salary 값을 나타냅니다. 조건문 IF를 사용하여 음수 값이 입력되지 않도록 하는 방식입니다. 

 

코드 실행 결과 : 음수 값인 -92891을 입력하려 했지만, 트리거 덕분에 salary가 0으로 자동으로 변경 됩니다.

---

BEFORE UPDATE 트리거 예시

DELIMITER $$

CREATE TRIGGER trig_upd_salary
BEFORE UPDATE ON salaries
FOR EACH ROW
BEGIN 
	IF NEW.salary < 0 THEN 
		SET NEW.salary = OLD.salary; 
	END IF; 
END $$

DELIMITER ;

이 트리거는 salaries 테이블의 salary 값을 업데이트할 때, 새로 입력된 값이 음수이면 기존 값(OLD.salary)으로 유지되도록 설정하는 트리거입니다.

이를 통해 음수 값으로 업데이트되는 것을 방지 할 수 있습니다.

UPDATE salaries SET salary = -50000 WHERE emp_no = '10001' AND from_date = '2010-06-22';
SELECT * FROM salaries WHERE emp_no = '10001' AND from_date = '2010-06-22';

실행 결과: 음수 값인 -50,000으로 업데이트하려 해도, 기존 값이 유지되도록 트리거가 동작합니다.

---

AFTER INSERT 트리거 예시

DELIMITER $$

CREATE TRIGGER trig_ins_dept_mng
AFTER INSERT ON dept_manager
FOR EACH ROW
BEGIN
	DECLARE v_curr_salary int;
    
    SELECT 
		MAX(salary)
	INTO v_curr_salary FROM
		salaries
	WHERE
		emp_no = NEW.emp_no;

	IF v_curr_salary IS NOT NULL THEN
		UPDATE salaries 
		SET 
			to_date = SYSDATE()
		WHERE
			emp_no = NEW.emp_no and to_date = NEW.to_date;

		INSERT INTO salaries 
			VALUES (NEW.emp_no, v_curr_salary + 20000, NEW.from_date, NEW.to_date);
    END IF;
END $$

DELIMITER ;

이 트리거는 dept_manager 테이블에 새로운 관리자가 삽입되면 자동으로 해당 직원의 급여를 20000을 증가시키고, 새 계약의 시작 날짜를 설정합니다. 기존 salary테이블에서 최대 급여를 가져와 새로운 급여를 계산 한 후, 그 값을 삽입합니다.

 

INSERT INTO dept_manager VALUES ('111534', 'd009', date_format(sysdate(), '%y-%m-%d'), '9999-01-01');
SELECT * FROM dept_manager WHERE emp_no = 111534;
SELECT * FROM salaries WHERE emp_no = 111534;

새로운 관리자로 등록된 직원의 급여가 $20,000 증가한 것을 확인할 수 있습니다.

---

시스템 함수

MySQL에는 사전 정의된 시스템 변수 외에도 시스템 함수가 존재합니다. 자주 사용되는 시스템 함수로는 SYSDATE()가 있으며, 이는 함수가 호출된 현재의 날짜와 시간을 반환합니다.

SELECT SYSDATE();
SELECT DATE_FORMAT(SYSDATE(), '%y-%m-%d') as today;

SYSDATE()는 현재 시간을, DATE_FORMAT()은 원하는 형식으로 날짜를 반환합니다.