33. MySQL 스토어드 프로그램 - 트리거 개념과 실습

트리거

트리거는 사전적 의미로 '방아쇠'를 뜻한다. 방아쇠를 당기면 '자동'으로 총알이 나가듯이, 트리거는 테이블에 무슨 일이 일어나면 '자동'으로 실행된다.

트리거는 기본적인 개념만 잘 파악하고 있다면 사용이 그다지 어렵지 않지만, 몇 가지 주의해야 할 점이 있다. 우선 트리거란 테이블에 삽입, 수정, 삭제 등의 작업(이벤트)이 발생 시에 자동으로 작동되는 개체로, 이번 chapter에서 배웠던 스토어드 프로시저와 비슷한 모양을 갖는다.

트리거에 많이 활용되는 사례 중 하나를 생각해 보자. 만약 누군가 A라는 테이블에 행을 고의 또는 실수로 삭제한다면, 삭제된 행의 내용을 복구하는 것도 어렵고, 누가 지웠는지 추적하는 것도 쉬운 일이 아니다. 이러한 경우에 A테이블에서 행이 삭제되는 순간에 삭제된 행의 내용, 시간, 삭제한 사용자 등을 B테이블에 기록해 놓는다면 이러한 문제점을 해결할 수 있을 것이다. 추후 문제 발생 시에는 B테이블의 내용을 확인하면 되기 때문이다. 이번 chapter에서 배울 트리거가 바로 이러한 기능을 수행할 수 있다.

트리거의 개요

chapter8에서 데이터의 무결성을 위한 제약 조건(Primary Key, Foreign Key)을 공부했었다. 트리거는 제약 조건과 더불어 데이터 무결성을 위해서 MySQL에서 사용할 수 있는 또 다른 기능이다.

트리거는 테이블에 관련되어 DML문(Insert, Update, Delete 등)의 이벤트가 발생될 때 작동하는 데이터베이스 개체 중 하나다.

트리거는 테이블에 부착되는 프로그램 코드라고 생각하면 된다. 스토어드 프로시저와 거의 비슷한 문법으로 그 내용을 작성할 수 있다. 그리고, 트리거가 부착된 테이블에 이벤트(입력, 수정, 삭제)가 발생하면 자동으로 부착된 트리거가 실행된다.

• 다른 일부 DBMS에서는 View에도 트리거를 부착할 수 있지만, MySQL은 View에 트리거를 부착할 수 없다.

트리거는 스토어드 프로시저와 작동이 비슷하지만 직접 실행시킬 수는 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다. 그리고 트리거에는 스토어드 프로시저와 달리 IN, OUT 매개 변수를 사용할 수도 없다.

우선 간단한 트리거를 보고 그 작동에 대해 이해해보자. 아직은 문법이 이해가 가지 않을 것이다. 그냥 작동되는 결과만 확인해 보자.

<실습>

CREATE DATABSE IF NOT EXISTS testDB;
USE testDB;
CREATE TABLE IF NOT EXISTS testTbl (id INT, txt VARCHAR(10));
INSERT INTO testTbl VALUES(1, '레드벨벳');
INSERT INTO testTbl VALUES(2, '잇지');
INSERT INTO testTbl VALUES(3, '블랙핑크');

DROP TRIGGER IF EXISTS testTrg;
DELIMITER //
CREATE TRIGGER testTrg -- 트리거 이름
	AFTER DELETE -- 삭제후에 작동하도록 지정
    ON testTbl -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용시킴
BEGIN
	SET @msg = '가수 그룹이 삭제됨'; -- 트리거 실행시 작동되는 코드들
END //
DELIMITER;

SET @msg = '';
INSERT INTO testTbl VALUES(4, '마마무');
SELECT @msg;
UPDATE testTbl SET txt = '블핑' WHERE id = 3;
SELECT @msg;
DELETE FROM testTbl WHERE id = 4;
SELECT @msg;

INSERT

UPDATE

DELETE

트리거의 종류

트리거(정확히는 DML 트리거)는 다음과 같이 구분할 수 있다.

AFTER 트리거
테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거를 말하며 이름이 뜻하는 것처럼 해당 작업 후에 작동한다.

BEFORE 트리거
이벤트가 발생하기 전에 작동하는 트리거다. AFTER 트리거와 마찬가지로 INSERT, UPDATE, DELETE 세 가지 이벤트로 작동한다.

트리거의 사용

MySQL 메뉴얼에 나오는 트리거의 문법은 다음과 같다.

trigger_time에서 BEFORE와 AFTER를 지정할 수 있으며 trigger_event는 INSERT/UPDATE/DELETE 중에 하나를 지정할 수 있다. trigger_order는 테이블에 여러 개의 트리거가 부착되어 있을 때, 다른 트리거보다 먼저 또는 나중에 수행되는 것을 지정한다.

삭제는 DROP TRIGGER를 사용하면 된다. 참고로 트리거는 ALTER TRIGGER문을 사용할 수 없다.

AFTER 트리거의 사용
sqlDB에 고객 테이블(userTbl)에 입력된 회원 정보가 종종 변경되지만 누가 언제 그것을 변경했고, 또 변경 전에 데이터는 어떤 것이었는지 알 필요가 있다면 다음 실습과 같이 트리거를 활용할 수 있다.

<실습>
회원 테이블에 update나 delete를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록해 놓자.

sqlDB 초기화~

DROP DATABASE IF EXISTS sqldb; -- 만약 sqldb가 존재하면 우선 삭제한다.
CREATE DATABASE sqldb;

CREATE TABLE usertbl -- 회원 테이블
( userID	CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  name		VARCHAR(10) NOT NULL, -- 이름
  birthYear	INT NOT NULL, -- 출생년도
  addr		CHAR(2) NOT NULL, -- 지역(경기, 서울, 경남 식으로 2글자만 입력)
  mobile1	CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
  mobile2	CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외)
  height	SMALLINT, -- 키
  mDate		DATE -- 회원 가입일
);
CREATE TABLE buytbl -- 회원 구매 테이블(Buy Table의 약자)
( num	int AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
  userid	CHAR(8) NOT NULL, -- 아이디(FK)
  prodName	CHAR(6) NOT NULL, -- 물품명
  groupName	CHAR(4), -- 분류
  price		INT NOT NULL, -- 단가
  amount	SMALLINT NOT NULL, -- 수량
  FOREIGN KEY (userID) REFERENCES usertbl(userID)
);

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', NULL, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1871, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30, 2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200, 5);

USE sqlDB;
DROP TABLE buyTbl; -- 구매테이블은 실습에 필요없으므로 삭제.
CREATE TABLE backup_userTbl
( userID	CHAR(8)	NOT NULL PRIMARY KEY,
  name		VARCHAR(10) NOT NULL,
  birthYear	INT NOT NULL,
  addr		CHAR(2) NOT NULL,
  mobile1	CHAR(3),
  mobile2	CHAR(8),
  height	SMALLINT,
  mDate		DATE,
  modType	CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
  modDate	DATE, -- 변경된 날짜
  modUser	VARCHAR(256) -- 변경한 사용자
);

DROP TRIGGER IF EXISTS backUserTbl_UpdateTrg;
DELIMITER //
CREATE TRIGGER backUserTbl_updateTrg -- 트리거 이름
	AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON userTbl -- 트리거를 부착할 테이블
    FOR EACH ROW
BEGIN
	INSERT INTO backup_userTbl VALUES(OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate, '수정', CURDATE(), CURRENT_USER() );
END //
DELIMITER;

DROP TRIGGER IF EXISTS backUserTbl_DeleteTrg;
DELIMITER //
CREATE TRIGGER backUserTbl_DeleteTrg -- 트리거 이름
	AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON userTbl -- 트리거를 부착할 테이블
    FOR EACH ROW
BEGIN
	INSERT INTO backup_userTbl VALUES(OLD.userID, OLD.name, OLD.birthYear, OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate, '삭제', CURDATE(), CURRENT_USER() );
END //
DELIMITER;

UPDATE userTbl SET addr = '몽고' WHERE userID = 'JKW';
DELETE FROM userTbl WHERE height >= 177;

SELECT * FROM backup_userTbl;

TRUNCATE TABLE userTbl;

SELECT * FROM backup_userTbl;

TRUNCATE는 트리거를 작동시키지 않는다는 것을 확인할 수 있다. TRUNCATE는 DML문이 아니라 DDL문이다.

DROP TRIGGER IF EXISTS userTbl_InsertTrg;
DELIMITER //
CREATE TRIGGER userTbl_InsertTrg -- 트리거 이름
	AFTER INSERT -- 입력 후에 작동하도록 지정
    ON userTbl -- 트리거를 부착할 테이블
    FOR EACH ROW
BEGIN
	SIGNAL SQLSTATE '45000' -- 사용자 오류를 강제로 발생
    	SET MESSAGE_TEXT = '데이터의 입력을 시도했습니다. 귀하의 정보가 서버에 기록되었습니다.';
END //
DELIMITER;

INSERT INTO userTbl VALUES('ABC', '에비씨', 1977, '서울', '011', '1111111', 181, '2019-3-3');