파트 8-3. 스토어드 프로그램 - 트리거
1. 트리거
트리거는 테이블에 무슨 일이 일어나면 자동으로 실행된다. 트리거는 테이블에 관련되어 DML문(삽입, 수정, 삭제 등)의 작업(이벤트)이 발생 시에 자동으로 작동되는 데이터베이스 개체 중 하나이다. 스토어드 프로시저와 작동이 비슷하지만 직접 실행시킬 수는 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다. 그리고 트리거에는 스토어드 프로시저와 달리 in, out 매개 변수를 사용할 수 없다. 또한 트리거는 alter로 수정이 불가능하므로 drop한 다음에 다시 작성해야 한다.
-- 예시
-- 실습하기 위한 테이블 생성과 정보 삽입
CREATE DATABASE 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; -- 가수 그룹이 삭제됨 출력
2. 트리거 종류
- after 트리거 : 테이블에 insert, update, delete 등의 작업이 일어났을 때 작동하는 트리거를 말하며 이름 그대로 작업 후에 작동한다.
- before 트리거 : 이벤트가 발생하기 전에 작동하는 트리거다. after트리거와 마찬가지로 insert, update, delete 세 가지 이벤트로 작동한다.
회원 테이블에 update나 delete를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록하는 실습을 해보자.
-- 실습을 위해 백업 테이블 생성
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
-- old는 update되기 전에 데이터를 의미함
INSERT INTO backup_userTbl VALUES( OLD.userID, OLD.name, OLD.birthYear,
OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate,
'수정', CURDATE(), CURRENT_USER() ); -- 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, delete 실행 시 트리거에 따라 backup테이블에 정보들이 삽입됨
UPDATE userTbl SET addr = '몽고' WHERE userID = 'JKW';
DELETE FROM userTbl WHERE height >= 177;
SELECT * FROM backup_userTbl; -- 확인
-- truncate는 dml문이 아니라 ddl문으로 트리거가 작동안함
TRUNCATE TABLE userTbl;
SELECT * FROM backup_userTbl; -- truncate 내용은 저장이 안되서 그대로임
-- 삽입이 안되게 만드는 트리거
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-12-25');
3. 트리거 임시 테이블
트리거에서 insert, update, delete 작업이 수행되면 임시로 사용되는 시스템 테이블이 두 개 있는데, new와 old이다. 다음과 같이 작동한다. 
new 테이블은 insert와 update 작업 시에 변경할 새로운 데이터를 잠시 저장해 놓는다. 즉, 테이블에 insert, update 트리거를 부착시켜 놓았을 때 명령이 수행되면, 입력/변경될 새 값이 new 테이블에 먼저 저장된 후에 new 테이블의 값을 실제 테이블에 입력/변경 하는 것이다. 그러므로 new 테이블을 조작하면 입력되는 새로운 값을 다른 값으로 대치시킬 수 있다. old 테이블은 delete와 update 작업 시 삭제,변경되기 전의 예전 값이 저장된다. 결론적으로 트리거의 작동 시에 새로 입력/변경되는 새로운 데이터를 참조하기 위해서는 new, 변경되기 전에 예쩐 데이터를 참조하기 위해서는 old 테이블을 참조하면 된다.
-- 예시
-- 삽입되는 값을 확인하고 조건에 부합하지 않으면 new값 수정하는 트리거
USE sqlDB;
DROP TRIGGER IF EXISTS userTbl_BeforeInsertTrg;
DELIMITER //
CREATE TRIGGER userTbl_BeforeInsertTrg -- 트리거 이름
BEFORE INSERT -- 입력 전에 작동하도록 지정
ON userTBL -- 트리거를 부착할 테이블
FOR EACH ROW
BEGIN
IF NEW.birthYear < 1900 THEN
SET NEW.birthYear = 0;
ELSEIF NEW.birthYear > YEAR(CURDATE()) THEN
SET NEW.birthYear = YEAR(CURDATE());
END IF;
END //
DELIMITER ;
INSERT INTO userTbl VALUES
('AAA', '에이', 1877, '서울', '011', '1112222', 181, '2022-12-25');
INSERT INTO userTbl VALUES
('BBB', '비이', 2977, '경기', '011', '1113333', 171, '2019-3-25');
select * from usertbl; -- 트리거대로 들어갔는지 확인
SHOW TRIGGERS FROM sqlDB; -- 트리거 내용 확인
-- 삭제
DROP TRIGGER userTbl_BeforeInsertTrg;
4. 기타 트리거에 관한 내용
다중 트리거
하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것을 말한다. 예로 after insert 트리거가 한 개 테이블에 2개 이상 부착되어 있을 수도 있다.
중첩 트리거
중첩 트리거란 트리거가 또 다른 트리거를 작동하는 것을 말한다. 
고객이 물건을 구매하면 구매한 기록이 구매 테이블에 insert된다. 구매 테이블에 부착된 insert 트리거가 작동되면서 이 트리거는 물품 테이블에서 구매한 개수만큼을 빼는 update를 한다. 이때 물품 테이블에 부탁된 update 트리거가 작동하여 배송 테이블에 배송할 내용을 insert한다. 이렇게 중첩트리거를 활용할 수 있다.
DROP DATABASE IF EXISTS triggerDB;
CREATE DATABASE IF NOT EXISTS triggerDB;
USE triggerDB;
CREATE TABLE orderTbl -- 구매 테이블
(orderNo INT AUTO_INCREMENT PRIMARY KEY, -- 구매 일련번호
userID VARCHAR(5), -- 구매한 회원아이디
prodName VARCHAR(5), -- 구매한 물건
orderamount INT ); -- 구매한 개수
CREATE TABLE prodTbl -- 물품 테이블
( prodName VARCHAR(5), -- 물건 이름
account INT ); -- 남은 물건수량
CREATE TABLE deliverTbl -- 배송 테이블
( deliverNo INT AUTO_INCREMENT PRIMARY KEY, -- 배송 일련번호
prodName VARCHAR(5), -- 배송할 물건
account INT UNIQUE); -- 배송할 물건개수
-- 물품 테이블 정보 삽입
INSERT INTO prodTbl VALUES('사과', 100);
INSERT INTO prodTbl VALUES('배', 100);
INSERT INTO prodTbl VALUES('귤', 100);
-- 물품 테이블에서 개수를 감소시키는 트리거
DROP TRIGGER IF EXISTS orderTrg;
DELIMITER //
CREATE TRIGGER orderTrg -- 트리거 이름
AFTER INSERT
ON orderTBL -- 트리거를 부착할 테이블
FOR EACH ROW
BEGIN
UPDATE prodTbl SET account = account - NEW.orderamount
WHERE prodName = NEW.prodName ;
END //
DELIMITER ;
-- 배송테이블에 새 배송 건을 입력하는 트리거
DROP TRIGGER IF EXISTS prodTrg;
DELIMITER //
CREATE TRIGGER prodTrg -- 트리거 이름
AFTER UPDATE
ON prodTBL -- 트리거를 부착할 테이블
FOR EACH ROW
BEGIN
DECLARE orderAmount INT;
-- 주문 개수 = (변경 전의 개수 - 변경 후의 개수)
SET orderAmount = OLD.account - NEW.account;
INSERT INTO deliverTbl(prodName, account)
VALUES(NEW.prodName, orderAmount);
END //
DELIMITER ;
-- 삽입
INSERT INTO orderTbl VALUES (NULL,'JOHN', '배', 5);
-- 트리거가 잘 작동되었는지 확인
SELECT * FROM orderTbl;
SELECT * FROM prodTbl;
SELECT * FROM deliverTbl;
-- 만약 중복 트리거에 관련된 열 이름을 바꾸면 어떻게 될까?
ALTER TABLE deliverTBL CHANGE prodName productName VARCHAR(5);
-- 오류 발생 -> 즉, 중복 트리거에서 하나라도 오류 발생하면 전부 롤백(취소) -> 오류
INSERT INTO orderTbl VALUES (NULL, 'DANG', '사과', 9);
-- 위에 insert가 아무런 영향을 주지 못했음 -> 오류이기 때문
SELECT * FROM orderTbl;
SELECT * FROM prodTbl;
SELECT * FROM deliverTbl;
트리거 작동 순서
[follows | precedes] 트리거이름
‘follows 트리거이름’을 지정하면 지정한 트리거가 작동한 다음에 현재 트리거가 작동되며, ‘precedes 트리거이름’을 지정하면 지정한 트리거가 작동하기 이전에 현재 트리거가 작동한다.
