[MySQL] 11. Trigger

 

 

 

Trigger

 

Trigger(트리거)는 테이블 내 데이터가 변동되는 작업인 INSERT, UPDATE, DELETE가 실행될 때 자동으로 함께 실행되는 코드를 의미한다.

 

실습을 위해 trigger_mart라는 새로운 DB를 셍성하고, school과 student라는 테이블을 추가해 주었다. school은 반이름과 공석이 저장되며 기본값으로 20이라는 공석의 값을 주었다. 

학생은 auto_increment를 활용해 학생이 한 명 추가될 때마다 학생 번호가 1씩 늘어난다.

CREATE TABLE trigger_mart.school(
  반이름 varchar(100)
  공석 int DEFAULT 20
);
CREATE TABLE trigger_mart.student(
  학생이름 varchar(100),
  학생번호 int AUTO_INCREMENT PRIMARY KEY 
)

1반의 공석을 20명으로 설정해준다.

 

 

만일 student에 학생이 1명 추가될 때마다, school 테이블의 1반 공석이 1자리씩 줄어들게 하고 싶으면 어떻게 해야할까? update문을 두개 작성해 사용할 수도 있겠지만 트리거를 사용할 수도 있다. 트리거는 어떠한 테이블에 INSERT, UPDATE, DELETE작업이 일어날 때 다른 작업이 자동으로 함께 이루어지도록 해주기 때문이다. 

 

DROP TRIGGER IF EXISTS DB이름.트리거이름;
DELIMITER $$
CREATE TRIGGER DB이름.트리거이름
-- 아래의 DB이름.테이블명 테이블에 INSERT/UPDATE/DELETE 작업이 일어난 전(=BEFORE) 또는 전(=AFTER)에
-- BEGIN-END 사이의 코드를 실행한다.
AFTER INSERT ON DB이름.테이블명 
FOR EACH ROW 
BEGIN
-- 	AFTER INSERT/UPDATE/DELETE ON 옆의 '테이블명' 테이블에 작업이 일어나기 전
-- 	이곳에 있는 코드가 실행된다.
	UPDATE trigger_mart.school SET 공석 = 공석-1 WHERE 반이름='1반';

END $$
DELIMITER ;

AFTER INSERT ON 테이블명 에서 AFTER의 자리에는 AFTER과 BEFORE이 올 수 있다.  AFTER이 오면 작업이 일어난 후에 BEGIN과 END 사이에 있는 코드가 실행되며, BEFORE이 오면 작업이 일어나기 전에 BEGIN과 END 사이의 코드가 실행된다.

 

AFTER INSERT ON 테이블명 에서 INSERT의 자리에는 INSERT, UPDATE, DELETE가 올 수 있다. ON 옆에 오는 테이블명의 테이블에 지정한 INSERT 또는 UPDATE, 또는 DELETE 작업이 일어날 때마다 BEGIN과 END사이의 코드가 실행된다. 

 

BEGIN과 END 사이에는 위의 [ AFTER INSERT ON 테이블명 ] 에서 지정한 작업이 실행되기 전이나 후에 자동으로 실행될 코드를 작성하는 공간이다. 이를 활용해 ' student 테이블에 INSERT작업이 일어날 때마다, school의 1반 공석의 숫자를 -1 해주세요 ' 라는 트리거를 작성해 보았다.

 

SQL 스크립트로 트리거를 추가하면, 해당 테이블의 트리거 폴더에 트리거가 추가된다.

학생을 한명 추가하자, 공석이 자동으로 -1 되었다.

 

 

트리거의 BEGIN과 END 사이에는 OLD, NEW라는 키워드를 이용해 다양한 기능을 사용할 수 있다. OLD에는 변경 전 데이터가, NEW에는 변경 후의 데이터가 들어있다. 

...
BEGIN
-- 	AFTER INSERT/UPDATE/DELETE ON 옆의 테이블에 작업이 일어나기 전
-- 	이곳에 있는 코드가 실행된다.
-- 변경 전 데이터가 들어있는 OLD
	OLD 
-- 	변경 후 데이터가 들어있는 new
	NEW 
END
...

 

BEFORE, AFTER 키워드와 OLD, NEW를 활용해 데이터를 유연하게 다룰 수 있다. 만일 학생의 이름이 6자 이상인 경우에는, 학생의 이름을 왼쪽부터 5글자 까지만 잘라서 저장하도록 BEFORE과 IF, NEW키워드를 사용해 트리거를 작성한다.

 

DROP TRIGGER IF EXISTS trigger_mart.join_student;
DELIMITER $$
CREATE TRIGGER trigger_mart.join_student
-- student 테이블에 INSERT/UPDATE/DELETE 작업이 일어난 전(=BEFORE) 또는 전(=AFTER)에
-- 아래의 BEGIN-END 사이의 코드를 실행한다.
BEFORE INSERT ON trigger_mart.student 
FOR EACH ROW 
BEGIN
-- 	AFTER INSERT/UPDATE/DELETE ON 옆의 테이블에 작업이 일어나기 전
-- 	이곳에 있는 코드가 실행된다.
	IF LENGTH (NEW.학생이름) > 5 THEN
		SET NEW.학생이름 = LEFT(NEW.학생이름,5);
	END IF;		

END $$
DELIMITER ;

 

'김하나빛나리' 라는 6글자의 학생 이름을 INSERT하여 트리거가 자동으로 수행되는지 테스트한다.

 

INSERT INTO trigger_mart.student(학생이름) VALUES ('김하나빛나리');

이름이 6글자이기 때문에, 왼쪽에서 5글자까지만 잘라져 저장되었다.