00502025년 3월 1일

[혼자공부하는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) 예시

SQL

-- 기본 세팅
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, '블랙핑크');

SQL

-- 트리거 생성
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) 기본 세팅

SQL

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) 트리거 생성

SQL

-- 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) 작동

SQL

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;