[SQL] 트리거

목차

• 트리거
• 트리거 활용
• 트리거가 사용하는 임시 테이블

 

트리거

트리거

• 트리거(trigger)는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해줌
• 트리거는 테이블에 INSERT 나 UPDATE 또는 DELETE 작업이 발생하면 실행되는 코드다. 

 

트리거의 기본 작동

• 트리거는 테이블에서 DML 문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다.
• 테이블에 미리 부착되는 프로그램 코드라고 생각하면 된다.
• 스토어드 프로시저와 달리 트리거에는 IN, OUT 매개변수를 사용할 수 없다.
• AFTER 트리거 기준으로 설명한다.

 

테이블 생성

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

 

 

트리거 부착

DELIMITER $$
CREATE TRIGGER myTrigger -- 트리거 이름지정
    AFTER DELETE -- DELETE 문이 발생한 이후에 작동하라
    ON trigger_table -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용시킨다.
BEGIN
    SET @msg = '가수 그룹이 삭제됨' ; -- 트리거 실행 시 작동되는 코드들
END $$
DELIMITER ;

 

 

INSERT, UPDATE 문 실행

SET @msg = '';
INSERT INTO trigger_table VALUES (4, '마마무');
SELECT @msg;

UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg;

 

 

DELETE 문 실행

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

 

 

 

트리거 활용

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

 

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

 

1. 가수 테이블 생성

CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

 

2. 백업 테이블 생성

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, -- 변경된 날짜
    modeUser VARCHAR(30) -- 변경한 사용자
);

 

3 - 1. 트리거 부착

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 ;

• OLD 테이블은 UPDATE 나 DELETE가 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블이다.
• OLD 테이블에 UPDATE 문이 작동되면 이 행에 의해서 업데이트 전의 데이터가 백업 테이블에 입력된다.
• OLD 테이블은 MySQL에서 내부적으로 제공되는 테이블이다.

 

3 - 2. 트리거 부착

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 ;

• 하나의 테이블에 여러 개의 트리거를 부착해도 된다.

 

4. 데이터 변경

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

 

 

 5. 백업 테이블 조회

SELECT * FROM backup_singer;

• 수정 또는 삭제된 내용이 잘 보관되어 있다.

 

6. 모든 행 데이터 삭제

TRUNCATE TABLE singer;

• DELETE 문 대신 TRUNCATE TABLE로 삭제

 

7. 백업 테이블 확인

SELECT * FROM backup_singer;

• DELETE 트리거는 오직 DELETE 문에서만 작동하기 때문에 트리거가 작동하지 않았다.
• TRUNCATE 문은 DELETE 트리거를 작동시키지 않는다.

 

 

트리거가 사용하는 임시 테이블

• 테이블에 INSERT, DELETE, UPDATE 작업이 수행되면 임시로 사용되는 시스템 테이블이 2개 있다.
• 이름은 NEW 와 OLD 이다.
• 두 테이블은 사용자가 만드는 것이 아니고, MySQL이 알아서 생성하고 관리한다.

 

NEW 테이블

• 새 값은 테이블에 들어가기 전에 NEW 테이블에 잠깐 들어가 있다.
• INSERT 문이 실행되면 다음과 같이 작동한다.

 

 

OLD 테이블

• 삭제될 예전 값이 삭제되기 전에 OLD 테이블에 잠깐 들어가 있다.
• 그래서 AFTER DELETE 트리거를 만들어도 삭제된 후에 OLD.열_이름 형식으로 예전 값에 접근할 수 있었다.

 

• UPDATE 를 사용하면 그림과 같이 NEW 테이블과 OLD 테이블을 모두 사용한다.

 

 

 

 

 

 

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

이것이MySQL이다 : 네이버 카페