트리거가 생성하는 임시 테이블
트리거에서 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블이 두 개 있는데, 이름은 'NEW'와 'OLD'이다. 두 테이블은 [그림 10-15]와 같이 작동한다.
[그림 10-15] 트리거의 NEW와 OLD 테이블의 작동 개념
[그림 10-15]를 보면 NEW 테이블은 INSERT와 UPDATE 작업 시에 변경할 새로운 데이터를 잠깐 저장해 놓는다. 즉, 테이블에 INSERT 트리거나 UPDATE 트리거를 부착시켜 놓았거나, 해당 테이블에 INSERT나 UPDATE 명령이 수행되면, 입력/변경될 새 값이 NEW 테이블에 먼저 저장된 후에, NEW 테이블의 값을 테이블에 입력/변경되는 것이다. 그러므로 NEW 테이블을 조작하면 입력되는 새로운 값을 다른 값으로 대치시킬 수 있다. OLD 테이블은 DELETE와 UPDATE 작업이 수행되면, 삭제 또는 변경되기 전의 예전 값이 저장된다. 결론적으로 트리거의 작동 시에 새로 입력/변경되는 새로운 데이터를 참조하기 위해서는 NEW 테이블을 확인하면 되고, 변경되기 전인 예전 데이터를 참조하기 위해서는 OLD 테이블을 참조하면 된다.
BEFORE 트리거의 사용
BEFORE 트리거는 테이블에 변경이 가해지기 전에 작동되는 트리거이다. BEFORE 트리거의 좋은 활용 예는 BEFORE INSERT 트리거를 부착해 놓으면 입력될 데이터 값을 미리 확인해서 문제가 있을 경우에 다른 값으로 변경시킬 수 있다.
<실습>
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', '비이', 1977, '경기', '011', '1113333', 171, '2019-3-25');
SELECT * FRoM userTBL;
SHOW TRIGGERS FROM sqlDB;
DROP TRIGGER userTbl_BeforeInsertTrg;
SELECT * FRoM userTBL; 의 결과
SHOW TRIGGERS FROM sqlDB; 의 결과
기타 트리거에 관한 내용
다중 트리거
하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것을 말한다. 예로 AFTER INSERT 트리거가 한 개 테이블에 2개 이상 부착되어 있을 수도 있다.
중첩 트리거
트리거가 또 다른 트리거를 작동하는 것을 말한다. [그림 10-18]을 보면서 이해하자.
- 고객이 물건을 구매하면 물건을 구매한 기록이 '구매 테이블'에 INSERT된다.
- '구매 테이블'에 부착된 INSERT 트리거가 작동한다. 내용은 '물품 테이블'의 남은 개수를 구매한 개수만큼 빼는 UPDATE를 한다. (인터넷 쇼핑몰에서 물건을 구매하면, 즉시 남은 수량이 하나 줄어드는 것을 보았을 것이다.)
- '물품 테이블'에 장착된 UPDATE 트리거가 작동한다. 내용은 '배송 테이블'에 배송할 내용은 INSERT하는 것이다.
하나의 '물건 구매'(INSERT) 작업으로 2개의 트리거가 연속적으로 작동했다.
INSERT 작업이 실패한다면 그 앞의 모든 작업은 자동으로 ROLLBACK 된다. 이것은 시스템에 부담이 되므로 성능에 나쁜 영향을 끼칠 소지가 있다.
- MySQL은 중첩 트리거는 지원하지만, 재귀 트리거는 지원하지 않는다. 다른 DBMS에서는 재귀 트리거를 지원하기도 한다.
트리거의 작동 순서
하나의 테이블에 여러 개의 트리거가 부착되어 있다면, 트리거의 작동 순서를 지정할 수 있다. 트리거의 문법을 다시 한 번 살펴보면 본체 바로 위에 다음과 같은 옵션을 지정할 수 있다.
'FOLLOWS 트리거이름'을 지정하면 지정한 트리거 다음에 현재 트리거가 작동되며 'PRECEDES 트리거이름'을 지정하면 지정한 트리거가 작동하기 이전에 현재 트리거가 작동된다는 의미다.
하나의 테이블에 여러 개의 트리거가 부착되어 있을 때, 대부분 순서는 큰 의미가 없지만 특별히 순서를 지정해야 할 경우에 이 옵션을 사용하면 된다.
<실습>
중첩 트리거의 작동을 실습해 보자.
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);
SELECT * FROM orderTbl;orderTbl에 주문이 안된 것을 확인할 수 있다. 즉 트리거는 모두 다 실행이되거나 전부 실행이 안되게끔 설정이 되어 있다.
