📒 요약 : 트리거는 INSERT, UPDATE, DELETE문이 작동할 때 자동으로 실행되는 프로그래밍 기능이다. 예를 들어, 트리거를 활용하면 데이터가 삭제될 때 해당 데이털르 달느 곳에 자동으로 백업할 수 있다.
자동 실행되는 트리거
1. 트리거 기본
트리거의 개요
트리거는 INSERT, UPDATE, DELETE문이 작동할 때 자동으로 실행되는 프로그래밍 기능이다. 시스템이 자동으로 수행하기 때문에 사용자가 추가 작업을 잊어버리는 실수를 방지한다. 즉, 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있다. 따라서 트리거를 잘 활용하면 데이터의 무결성을 지킬 수 있다.
트리거의 기본 작동
트리거는 테이블에서 INSERT, UPDATE, DELECT등과 같은 DML(Data Manipulation Language)문의 이벤트가 발생할 때 작동한다. 테이블에 미리 부착되는 프로그램 코드라고 생각하자. 트리거는 스토어드 프로시저와 문법이 비슷하지만, 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 ;이제 이 테이블에서 DELETE문을 실행하면 트리거가 작동되어 @msg 변수에 트리거에서 설정한 내용이 입력되는 것을 확인할 수 있다. 이렇게 트리거는 테이블에 부착해서 사용할 수 있다. 지금은 간단하게 메시지를 출력하는 기능을 만들었지만 실제로는 복잡하는 SQL문들로 트리거를 만들면 매우 유용하게 작동할 것이다.
DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;
2. 트리거 활용
트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있다. 예를 들어, 은행의 창구에서 새로 계좌를 만들 때에는 INSERT를 이용할 것이다. 계좌에 입금하거나 출금하면 UPDATE문으로 값을 변경하며, 계좌를 해지하면 DELETE가 작동한다. 그런데 이러한 행동들을 누가 했는지 알 수 없다면 나중에 계좌에 문제가 발생했을 때 원인을 파악할 수 없을 것이다. 이럴 때를 대비해서 데이터에 변경이 발생할 때, 트리거를 자동으로 작동시켜 데이터를 변경한 사용자와 시간 등을 기록할 수 있다.
이러한 개념을 저용하여 marker_db의 member 테이블에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거를 작성해 보자. 이번에 사용할 테이블은 아래와 같다.
USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);
SELECT * FROM singer;
가수 테이블에 INSERT나 UPDATE 작업이 일어나는 경우, 변경되기 전의 데이터를 저장할 백업 테이블을 미리 생성해둔다. 백업테이블에는 추가로 수정 또는 삭제인지 구분할 변경된 타입(modType), 변경된 날짜(modDate), 변경한 사용자(modUser)를 추가한다.
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문과 DELETE문이 발생할 때 작동되는 트리거를 singer 테이블에 부착해보자. 먼저 UPDATE가 발생했을 때 작동하는 singer_updateTrg 트리거를 만들어보자.
DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg -- 트리거 이름
AFTER UPDATE -- 변경 후에 작동하도록 지정
ON singer -- 트리거를 부착할 테이블
FOR EACH ROW
BEGIN
#OLD 테이블은 UPDATE나 DELETE가 수행될 때 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블이다. 원래 데이터를 보존할 수 있다.
INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number,
OLD.addr, '수정', CURDATE(), CURRENT_USER() ); #CURDATE()는 현재 날짜를, CURRENT_USER()는 현재 작업 중인 사용자를 알려준다.
END $$
DELIMITER ;이번에는 DELETE가 발생했을 때 작동하는 singer_deleteTrg 트리거를 생성해보자. singer_updateTrg와 거의 비슷하다. 차이점은 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 ;이제 데이터를 변경해보자. 한 건의 데이터를 업데이트하고, 여러 건을 삭제한 뒤, 변경한 내용이 잘 보관되어 있는지 backup_singer 테이블을 조회한다.
UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;
3. 트리거가 사용하는 임시 테이블
테이블에 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블이 2개가 있는데, 이름은 NEW와 OLD이다. 두 테이블은 사용자가 만드는 것이 아니고, MySQL이 알아서 생성하고 관리하므로 사용자가 따로 신경 쓸 필요는 없다. 먼저 NEW 테이블은 INSERT 문이 실행되면 새 데이터가 테이블에 들어가기 전에 임시로 저장되어 있는 테이블이다. OLD 테이블도 마찬가지로 DELETE 문이 실행되었을 때 데이터가 삭제되기 전에 들어가는 테이블이다. 마지막으로 UPDATE(새 값, 예전 값)을 사용하면 NEW 테이블과 OLD 테이블을 모두 사용한다.
