MySQL - 7.3 스토어드 프리시저 - 자동으로 실행되는 트리거

트리거란?

트리거는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다. 예를 들어 회사원이 퇴사하면 직원 테이블에서 삭제하면 된다. 그런데 나중에 퇴사한 직원이 회사에 다녔던 기록을 요청할 수도 있다. 이를 미리 예방하려면 직원 테이블에서 삭제하기 전에 퇴사자 테이블에 옮겨 놓아야 한다. 문제는 이런 작업을 수동으로 할 경우 백업하지 않고 데이터를 삭제할 수 있다는 것이다.

트리거는 이런 실수를 방지할 수 있다. 직원 테이블에서 사원을 삭제하면 해당 데이터를 자동으로 퇴사자 테이블에 들어가도록 설정할 수 있다. 즉, 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있다. 이런 것을 데이터의 무결성이라고 부르기도 한다.

트리거는 사전적 의미로 방아쇠를 뜻한다. 총의 방아쇠를 당기면 자동으로 총알이 나가듯이, 트리거는 테이블에 무슨 일이 일어나면 자동으로 실행된다.

(트리거 - 혼공SQL 교재)

트리거 기본

트리거의 개요

트리거란 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생하면 실행되는 코드이다.

위의 예시와 같이 회원 테이블에서 DELETE가 일어날 경우 해당 데이터가 삭제되기 전에 다른 곳에 자동으로 저장해주는 기능을 수행하는 용도이다.

트리거의 기본 작동

트리거는 따로 실행하는 것이 아니라 자동으로 실행되는 것!
트리거는 테이블에서 DML(Data Manipulation Language)문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다. 테이블에 미리 부착되는 프로그램 코드라고 생각하면 된다.
( * 여기서 언급하는 트리거는 AFTER트리거 이다. BEFORE 트리거는 작동 방식이 조금 다르고, 일반적으로 AFTER트리거를 많이 사용한다.)

(트리거 - 혼공SQL 교재)

트리거는 스토어드 프로시저와 문법이 비슷하지만, CALL문으로 직접 실행시킬 수는 없고 오직 테이블에 INSERT, UPDATE, DELETE 등의 이벤트가 발생할 경우에만 자동으로 실행된다. 또한 스토어드 프로시저와 달리 트리거에는 IN, OUT 매개변수를 사용할 수 없다.

트리거 기본 코드

• 트리거 생성

* 우선 테이블을 만든다
USE market_db;
CREATE TABLE IF NOT EXISTS trigger_table (id INT, txt VARCHAR(10));

* 테이블에 정보 삽입
INSERT INTO trigger_table VALUES(1, '레드벨벳');
INSERT INTO trigger_table VALUES(2, '잇지');
INSERT INTO trigger_table VALUES(3, '블랙핑크');

* 트리거 생성
DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$ 
CREATE TRIGGER myTrigger  -- 트리거 이름
    AFTER  DELETE -- 삭제후에 작동하도록 지정
    ON trigger_table -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용시킴(무조건 예약어로 써준다고 생각)
BEGIN
    SET @msg = '가수 그룹이 삭제됨' ; -- 트리거 실행시 작동되는 코드들
END $$ 
DELIMITER ;

• 트리거 작동 확인

SET @msg = '';
INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg;
UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg;

DELETE 실행시에만 msg에 자동 저장된다.

DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;

트리거 활용

트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있다.
(* 테이블에 이벤트가 먼저 적용된 후에 트리거가 실행된다.)

* 테이블 생성
USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

* 테이블 구성
DROP TABLE IF EXISTS backup_singer;
CREATE TABLE backup_singer
( mem_id  		CHAR(8) NOT NULL , 
  mem_name    	VARCHAR(10) NOT NULL, 
  mem_number    INT NOT NULL, 
  addr	  		CHAR(2) NOT NULL,
  modType  CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
  modDate  DATE, -- 변경된 날짜
  modUser  VARCHAR(30) -- 변경한 사용자
);

* 트리거 생성 - UPDATE
DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg  -- 트리거 이름
    AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '수정', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

* 트리거 생성 - DELETE
DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg  -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '삭제', CURDATE(), CURRENT_USER() ); -- 변경되기 전의 것이 저장되는 것이다.
END $$ 
DELIMITER 


* 트리거 사용
UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;

추가로 TRUNCATE TABLE을 통해 모든 데이터를 지우면

TRUNCATE TABLE singer;

SELECT * FROM backup_singer;

백업되지 않는다. INSERT UPDATE DELETE만 트리거를 작동시킨다.