트리거(Trigger)
데이터베이스 트리거(Trigger)란 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다. 즉 트리거는 작업을 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지하는 역할을 한다.
예를 들어 직원 테이블에서 사원을 삭제하면 해당 데이터를 자동으로 퇴사자 테이블에 들어가도록 트리거 설정하는 등의 동작을 자동적으로 수행할 수 있다.
트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있는데, 이것이 데이터의 무결성의 보장한다.
데이터의 무결성이란?
데이터의 무결성이란 데이터의 정확성과 일관성을 유지하고 보증하는 것을 가리킨다.
트리거 기본
트리거란 테이블에 INSERT, UPDATE, DELETE 작업이 발생 시 실행되는 코드이다. 예를 들어 특정 테이블에서 DELETE 연산이 일어난다면 다른 테이블에 자동으로 해당 정보를 백업해놓는 작업을 설정할 수 있다.
트리거의 작동은 다음과 같다.
- 테이블이 DML문(INSERT, UPDATE, DELETE)이 실행된다.
- 트리거에서 설정된 작업이 자동으로 작동한다.
트리거는 DML(Data Manipulation Language)문인 INSERT, UPDATE, DELETE 등의 이벤트가 발생할 때 작동한다. 이는 테이블에 미리 부착되는 프로그램 코드이며, 트리거는 BEFORE 트리거와 AFTER 트리거가 존재하며 일반적으로 AFTER 트리거가 많이 사용된다.
트리거는 언뜻보기에는 스토어드 프로시저와 비슷하나 CALL을 사용하여 직접 호출이 불가능하고 IN, OUT 매개 변수를 사용할 수 없다.
자세한 사용법은 아래에서 알아보도록 하자.
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 ;trigger_table을 생성하고, 해당 테이블에 myTrigger란 이름을 가진 트리거를 생성했다. AFTER DELETE ON 테이블명은 해당 테이블에서 DELETE 문이 발생된 이후 작동하라는 의미이다. FOR EACH ROW는 각 행마다 적용시킨다는 의미이며, BEGIN ~ END 사이에 실제로 작동할 쿼리문을 입력하면 된다.
<실행>
SET @msg = '';
INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg;
UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg;<결과>
DELETE에만 작동하는 트리거를 부착시켜 놓았으므로 INSERT, UPDATE에 트리거가 반응하지 않는다.
<실행>
DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;<결과>
DELETE 문을 실행 시 준비해놓은 트리거 작동하는 것을 확인할 수 있다.
트리거 활용
트리거는 테이블에 입력, 수정, 삭제되는 정보를 백업하는 용도로 활용할 수 있다. 테이블에 이벤트가 먼저 적용된 후에 트리거 작동하는 방식으로 백업을 해보도록 하자.
<실행>
USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);
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)
);
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 ;
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;
SELECT * FROM backup_singer;<결과>
OLD 테이블은 UPDATE나 DELETE가 수행될 때 변경되기 전의 데이터가 저장되는 임시 테이블로 MySQL에서 내부적으로 제공되는 테이블이다. CURDATE()는 현재 날짜, CURRENT_USER()는 현재 작업 중인 사용자를 반환한다. 하나의 테이블에 여러 개의 트리거를 부착할 수 있다.
DELETE와 UPDATE 트리거를 설정하고 실행 시, 백업 테이블에 정상적으로 변경 전 데이터가 저장된 것을 확인할 수 있다.
<실행>
TRUNCATE TABLE singer;
SELECT * FROM backup_singer;<결과>
TRUNCATE TABLE 테이블명은 테이블의 모든 행 데이터 제거하는 쿼리문으로 DELETE FROM 테이블명과 동일하나 더 빠르게 모든 행의 데이터를 제거할 수 있다. 그러나 singer 테이블에 부착된 트리거는 UPDATE와 DELETE일 뿐, TRUNCATE는 존재하지 않으므로 트리거가 작동하지 않는다.
정리하자면 테이블에 INSERT, UPDATE, DELETE 작업이 수행될 시 임시로 사용되는 시스템 테이블인 NEW와 OLD가 존재한다. NEW 테이블은 INSERT, UPDATE, DELETE 작업 수행 후 테이블 데이터 정보를 저장하고 있고, OLD 테이블은 INSERT, UPDATE, DELETE 작업 수행 전 테이블 데이터 정보를 저장하고 있다.
출처
- 혼자 공부하는 SQL(우재남 저, 한빛미디어)
