[혼자공부하는SQL] 20강

07-3 자동으로 실행되는 트리커

0. 시작하기 전에

• 트리거는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다.
• INSERT, UPDATE, DELETE가 실행되면, 방아쇠가 당겨져서 트리거를 자동으로 작동한다.
• ex) 직원 테이블에서 사원을 삭제하면, 해당 데이터를 자동으로 퇴사자 테이블에 들어가도록 설정할 수 잇다. 즉, 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있고, 이런 것을 데이터 무결성이라고 부르기도 한다.

1. 트리거의 기본

1) 트리거의 개요

• 트리거란? 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생하면 실행되는 코드이다.
• ex) 해당 데이터가 삭제 되기 전에 다른 곳으로 자동으로 저장해주는 기능이 있다면, 사용자는 더 이상 행 데이터를 삭제하기 전에 다른 곳에 저장해야 하는 부담에서 벗어나게 될 뿐 아니라, 모든 것이 자동으로 처리되므로 삭제된 모든 사용자 정보는 완벽하게 별도의 장소에 저장될 것이다.

2) 트리거의 기본 작동

(1) 설명

트리거는 테이블에서 DML(Data Manipulation Language)문인 INSERT, UPDATE, DELETE 등의 이벤트가 발생될 때 작동한다.

• 테이블에 미리 부착(attach)되는 프로그램 코드라고 생각하면 된다.
• 이 책에서 언급하는 트리거는 AFTER트리커이다. BEFORE 트리거는 작동 방식이 조금 다르다.

(2) 특징

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

(3) 예시

-- 기본 세팅
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 = '';

-- 트리거 발생 : X (INSERT, UPDATE)
INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg;
UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg;

-- 트리거 발생 : O (DELETE)
DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;

2. 트리거 활용

0) 상황

• 은행의 창구에서 새로 계좌를 만들 때 INSERT를 사용한다.
• 계좌에 입금하거나 출금하면 UPDATE를 사용해서 값을 변경하며, 계좌를 폐기하면 DELETE가 작동한다.
• 그런데 계좌라는 중요한 정보를 누가 입력/수정/삭제했는지 알 수 없다면, 나중에 계좌에 문제가 발생했을 때 원인을 파악할 수 없을 것이다.
• 이럴 때를 대비해서 데이터에 입력/수정/삭제가 발생할 때, 트리거를 자동으로 작동시켜, 데이터를 변경한 사용자의 시간 등을 기록할 수 있다.

→ 고객 테이블에 입력된 회원의 정보가 변경될 때, 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거를 만들어 보자.

1) 코드

(1) 기본 세팅

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) -- 변경한 사용자
);

(2) 트리거 생성

-- 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 ;

(3) 작동

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;

SELECT * FROM backup_singer;

-- 모든 데이터가 다 지워진다. (백업 테이블에 들어가지 않는다. 즉 트리거를 발생 시키지 않는다.)
TRUNCATE TABLE singer;

SELECT * FROM backup_singer;