[SQL] 07-3 자동 실행되는 트리거
스토어드 프로시저
자동 실행되는 트리거
트리거(trigger)는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다.
예를 들어 회사원이 퇴사하면 직원 테이블에서 삭제하면 된다.
그런데 나중에 퇴사한 직원이 회사에 다녔던 기록을 요청할 수도 있다.
이를 미리 예방하려면 직원 테이블에서 삭제하기 전에 퇴사자 테이블에 옮겨 놓아야 한다.
문제는 이런 작업을 수동으로 할 경우 백업하지 않고 데이터를 삭제할 수 있다는 것이다.
트리거는 이런 실수를 방지할 수 있다.
직원 테이블에서 사원을 삭제하면 해당 데이터를 자동으로 퇴사자 테이블에 들어가도록 설정할 수 있다.
즉, 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있으며, 이런 것을 데이터의 무결성이라고 부르기도 한다.
트리거 기본
트리거는 사전적 의미로 ‘방아쇠’를 뜻한다.
트리거는 테이블에 무슨 일이 일어나면 자동으로 실행된다.
트리거의 개요
트리거(trigger)란 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생하면 실행되는 코드이다.
예를 들어, market_db의 회원 중 ‘블랙핑크’가 탈회하는 경우를 생각해보겠다. 회원에서 탈퇴하면 블랙핑크의 행 데이터를 DELETE 문으로 지우면 된다.
그러면 블랙핑크는 데이터베이스에 존재하지 않아 회원 정보를 알 수 있는 방법이 없다.
이를 방지하는 방법은 블랙핑크의 행을 삭제하기 전에 그 내용을 다른 곳에 복사하는 것이다.
회원 테이블(member)에서 DELETE 작업이 일어날 경우 해당 데이터가 삭제되기 전에 다른 곳에 자동으로 저장해주는 기능이 있다면 삭제된 모든 사용자 정보는 완벽하게 별도의 장소에 저장될 것이다.
이것이 트리거의 대표적인 용도이다.
트리거의 기본 작동
트리거 테이블에서 DML(Data Manipulation Language) 문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다.
우선 간단한 트리거를 보기 위해 테스트로 사용할 테이블을 만들어보겠다.
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 ;
-
트리거 이름을 myTrigger로 지정한다.
-
AFTER DELETE는 이 트리거는 DELETE 문이 발생된 이후에 작동하라는 의미이다.
-
트리거를 부착할 테이블을 지정한다.
-
FOR EACH ROW는 각 행마다 적용시킨다는 의미인데, 트리거에는 항상 써준다고 보면 된다.
-
트리거에서 실제로 작동할 부분으로 @msg 변수에 글자를 대입시킨다.
이제 트리거를 부착한 테이블에 값을 삽입하고 수정해보겠다.
SET @msg = '';
INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg;
UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg;
결과를 보면 아무것도 나오지 않았다.
@msg 변수에 빈 문자를 넣고 INSERT 문을 실행했다.
그런데 trigger_table에는 DELETE에만 작동하는 트리거를 부착시켜 놓았다.
그러므로 트리거가 작동하지 않아서 빈 @msg가 그대로 출력된 것이다.
UPDATE 문도 마찬가지다.
이제 DELETE 문을 테이블에 적용시켜보겠다.
DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;
[Output]
|@msg| |—| 가수 그룹이 삭제됨
DELETE 문을 실행하니, 트리거가 작동한 것을 확인할 수 있다.
이렇게 트리거는 테이블에 부착해서 사용할 수 있다.
트리거 활용
트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있다.
다음과 같은 사례를 생각해보겠다.
은행의 창구에서 새로 계좌를 만들 때는 INSERT를 사용한다.
계좌에 입금하거나 출금하면 UPDATE를 사용해서 값을 변경하며, 계좌를 폐기하면 DELETE가 작동한다.
계좌에 문자가 발생했을 때를 대비해서 데이터에 입력/수정/사제가 발생할 때, 트리거를 자동으로 작동시켜 데이터를 변경한 사용자와 시간 등을 기록할 수 있다.
이런 개념을 적용해서 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거를 작성해보겠다.
회원 테이블의 열을 간단히 아이디, 이름, 인원, 주소 4개의 열로 구성된 가수 테이블(singer)로 복사해서 진행하겠다.
USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);
테이블에 변경되기 전의 데이터를 저장할 백업 테이블을 미리 생성하겠다.
백업 테이블에는 추가로 수정 또는 삭제인지 구분할 변경된 타입(modType), 변경된 날짜(modDate), 변경한 사용자(modUser)를 추가했다.
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
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 문이 작동되면 이 행에 의해서 업데이트되기 전의 데이터가 백업 테이블(backup_singer)에 입력된다.
즉, 원래 데이터가 보존된다. -
CURDATE()는 현재 날짜를, CURRENT_USER()는 현재 작업 중인 사용자를 알려준다.
이번에는 삭제(DELETE)가 발생했을 때 작동하는 singer_deleteTrg 트리거를 생성하겠다.
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;
방금 수정 또는 삭제된 내용이 잘 보관되어 있는지 결과를 확인해보겠다.
백업 테이블(backup_singer)을 조회해보겠다.
SELECT * FROM backup_singer;
1건이 수정되고 4건이 삭제된 것을 확인할 수 있다.
이번에는 DELETE 대신에 TRUNCATE TABLE 문으로 테이블의 모든 행 데이터를 삭제해보겠다.
TRUNCATE TABLE singer;
삭제가 잘 되었는지 백업 테이블을 다시 확인해보겠다.
SELECT * FROM backup_singer;
백업 테이블에 삭제된 내용이 들어가지 않았다.
이유는 TRUNCATE TABLE로 삭제 시에는 트리거가 작동하지 않기 때문이다.
DELETE 트리거는 DELETE 문에만 작동한다.