[MariaDB] 마리아DB 스토어드 프로그램 - 트리거 Trigger (MySQL)
스토어드 프로그램 종류는 4가지 입니다.
- 스토어드 프로시저 Stored Procedure
- 스토어드 함수 Stored Function
- 커서 Cursor
- 트리거 Trigger
이번 포스팅에서는 4번째인 트리거(Trigger)에 대해 설명하겠습니다.
트리거는 (1) AFTER 트리거, (2) BEFORE 트리거가 있습니다.
AFTER 트리거는 테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거 입니다. 작업이 이러난 후에 작동합니다.
BEFORE 트리거는 이벤트가 발생하기 전에 작동합니다. AFTER와 마찬가지로 INSERT, UPDATE, DELETE 이벤트로 작동합니다.
AFTER 트리거를 활용해 회원정보의 변경 또는 삭제 시 데이터를 백업하기
먼저 insert, update 작업이리 얼아나는 경우, 변경되기 전에 데이터를 저장할 테이블을 생성합니다.
USE testDB;
CREATE TABLE backup_userTBL -- 유저테이블의 변경전 내용을 저장할 테이블 생성
( userID char(8) NOT NULL PRIMARY KEY, -- ID를 기본키로 설정
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) -- 변경한 사용자 저장할 컬럼
);
변경과 삭제가 발생할 때 작동하는 트리거를 userTBL에 부착합니다.
변경이 발생했을 때 작동하는 backupUserTbl_UpdateTRG 트리거를 작성합니다.
DROP TRIGGER IF EXISTS backUserTbl_UpdateTrg; -- 트리거가 존재하면 삭제
DELIMITER // -- 스토어드 프로그램 시작
CREATE TRIGGER backUserTbl_UpdateTrg -- 트리거 생성
AFTER UPDATE -- 변경 후에 작동하도록 지정
ON userTBL -- 트리거를 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() ); -- OLD. 테이블은 update나 delete가 실행되기 전 잠깐 데이터가 저장되는 임시 테이블
END // -- 코드 끝
DELIMITER ; --스토어드 프로글램 끝
삭제가 발생했을 때 작동하는 backupUserTbl_DeleteTrg 트리거를 작성합니다.
DROP TRIGGER IF EXISTS backUserTbl_DeleteTrg;
DELIMITER //
CREATE TRIGGER backUserTbl_DeleteTrg -- 트리거 생성
AFTER DELETE -- 삭제 후에 작동하도록 지정
ON userTBL -- 트리거를 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 ;
다음은 usetTBL 테이블에서 UPDATE와 DELETE를 해봅니다.
UPDATE userTBL SET addr = '서울' WHERE userID = 'AAA';
DELETE FROM userTBL WHERE height >= 180;
마지막으로 userTBL과 backup_userTBL 테이블을 조회하여 변경 된 사항을 확인합니다.
SELECT * FROM userTBL; -- update, delete가 잘 실행되어 있음을 확인
SELECT * FROM backup_userTBL; -- update, delete 된 데이터를 보관하고 mod값들도 정상 작동 확인
트리거가 생성하는 임시테이블(NEW, OLD)
트리거에서는 INSERT, UPDATE, DELETE 작업 수행시 임시로 사용되는 시스템 테이블 NEW, OLD가 있습니다.
먼저, NEW 테이블은 INSERT, UPDATE 작업 시 변경할 새로운 데이터를 잠깐 저장해 둡니다.
그 후에 NEW 테이블의 값을 변경하고자 했던 테이블에 입력, 변경하는 것 입니다.
그래서 NEW 테이블의 값을 조작하면 변경하고자 했던 테이블의 최종 입력, 변경하는 값을 새로운 값으로 변경할 수 있습니다.
INSERT(새 값) -> NEW테이블(새 값 입력) -> 변경하고자한 테이블(새 값 입력)
다음으로 OLD 테이블은 DELETE, UPDATE 작업이 수행될때 삭제, 변경되기 전에 예전 값을 임시 저장하는 테이블입니다.
DELETE(예전 값) -> 변경하고자한 테이블(예전 값 삭제) -> OLD테이블(예전 값 존재)
UPDATE의 경우 위에 NEW와 OLD가 모두 발생하게 됩니다.
UPDATE(새 값, 예전 값) -> NEW테이블(새 값 입력) -> 변경하고자한 테이블(예전값 삭제, 새값 입력) -> OLD테이블(예전 값 존재)
트리거의 작동 시에 새로 입력/변경되는 새로운 데이터를 참조하기 위해서는 NEW 테이블을 참조하고, 변경되기 전의 예전 데이터를 참조하기 위해서는 OLD 테이블을 참조하면 됩니다.
BEFORE 트리거를 활용해 생년이 잘못 입력시 생년을 수정하여 입력하기
USE testDB;
DROP TRIGGER IF EXISTS userTBL_BeforeInsertTrg;
DELIMITER //
CREATE TRIGGER userTBL_BeforeInsertTrg -- 트리거 생성
BEFORE INSERT -- INSERT 전에 사용되도록
ON userTBL -- 트리거를 userTBL 테이블에 부착
FOR EACH ROW
BEGIN
IF NEW.birthYear < 1900 THEN -- NEW테이블의 생년 값이 1900년 미만으로 입력 시
SET NEW.birthYear = 0; -- NEW테이블의 생년 값을 0으로 설정
ELSEIF NEW.birthYear > YEAR(CURDATE()) THEN -- 또는, NEW테이블의 생년 값이 올해보다 초과할 때는
SET NEW.birthYear = YEAR(CURDATE()); -- NEW테이블의 생년 값을 올해년도로 설정
END IF;
END //
DELIMITER ;
INSERT 두개를 실행하여 생년을 잘못 입력시 BEFORE 트리거가 잘 작동하는지 확인합니다.
INSERT INTO userTBL VALUES('BBB', '비비비', 1777, '부산', '011', '2222222', 191, '2020-10-01'); -- 1900년 미만 생년 트리거 발생시킴
INSERT INTO userTBL VALUES('CCC', '시시시', 2999, '대구', '011', '3333333', 161, '2010-2-20'); -- 올해보다 높은 생년 트리거 발생시킴
userTBL 테이블을 확인하여 트리거가 정상작동 한 INSERT 값이 입력 되었는지 확인해 봅니다.
SELECT * FROM userTBL;
트리거 확인 및 트리거 삭제 방법
SHOW TRIGGERS FROM DB명; -- DB에 있는 트리거 보여줌
DROP TRIGGER 트리거명; -- '트리거명' 트리거를 삭제
중첩트리거 활용하기
활용 시나리오
- 구매 테이블, 물품 테이블, 배송 테이블 3개의 테이블이 존재합니다.
- 고객이 물건을 구매 시 구매테이블에 값이 INSERT 됩니다.
- 구매테이블에 값이 INSERT 될 경우 -> 트리거로 물품테이블의 물품 남은 갯수를 수정시킵니다.(UPDATE)
- 물품테이블의 값이 UPDATE 될경우 -> 트리거로 배송테이블에 배송할 물품명과 물품갯수를 입력합니다.(INSERT)
먼저 테이블들을 생성합니다.
DROP DATABASE IF EXISTS testDB; -- 만약 testDB DB가 존재한다면 삭제
CREATE DATABASE IF NOT EXISTS testDB; -- testDB 생성
USE testDB; --testDB 사용
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('냉장고', 999);
INSERT INTO prodTbl VALUES('TV', 999);
INSERT INTO prodTbl VALUES('세탁기', 999);
중첩트리거를 생성합니다.
-- 물품 테이블에서 개수를 감소시키는 트리거
DROP TRIGGER IF EXISTS orderTrg; -- 트리거가 존재한다면 삭제
DELIMITER // -- 스토어드 프로그램 시작
CREATE TRIGGER orderTrg -- 트리거 orderTrg 생성
AFTER INSERT -- 입력 후에 실행되도록 설정
ON orderTBL -- 트리거를 orderTBL 테이블에 부착
FOR EACH ROW
BEGIN
UPDATE prodTbl SET account = account - NEW.orderamount -- prodTbl 테이블의 account 값을 NEW테이블의 orderamount 값으로 빼서 저장
WHERE prodName = NEW.prodName ; -- prodName명이 NEW테이블의 prodName명과 같은 제품을 찾아서
END //
DELIMITER ;
-- 배송테이블에 새 배송 건을 입력하는 트리거
DROP TRIGGER IF EXISTS prodTrg;
DELIMITER //
CREATE TRIGGER prodTrg -- 트리거 prodTrg 생성
AFTER UPDATE -- 수정 후에 실행되도록 설정
ON prodTBL -- 트리거를 prodTBL 테이블에 부착
FOR EACH ROW
BEGIN
DECLARE orderAmount INT; -- 변수 orderAmount INT형으로 선언
SET orderAmount = OLD.account - NEW.account; -- 주문 개수 = (변경 전의 개수 - 변경 후의 개수)
INSERT INTO deliverTbl(prodName, account) -- deliverTbl테이블에 입력
VALUES(NEW.prodName, orderAmount); -- 입력값: NEW테이블의 제품명, 변수 orderAmount 값(주문개수)
END //
DELIMITER ;
물건을 구매했다고 가정하고 주문테이블에 값을 입력해봅니다.
INSERT INTO orderTbl VALUES (NULL,'AAA', '냉장고', 5);
중첩트리거가 잘 작동했는지 테이블들을 확인합니다.
SELECT * FROM orderTbl; -- 주문 INSERT가 입력되었는지 확인
SELECT * FROM prodTbl; -- oserTBL 테이블 INSERT 이후에 트리거가 작동했는지 확인.
SELECT * FROM deliverTbl; -- prodTBL 테이블 UPDATE 이후에 트리거가 작동했는지 확인.
만약 중첩트리거가 작동 중 마지막 트리거가 실패 할 경우 앞선 트리거들도 모두 롤백이 됩니다.
'MariaDB' 카테고리의 글 목록
전산 관련 경험을 기록 하는 곳
reddb.tistory.com
'MariaDB' 카테고리의 다른 글
| [MariaDB] 마리아DB PHP - XAMPP 설치하기 (MySQL) (0) | 2020.10.19 |
|---|---|
| [MariaDB] 마리아DB 스토어드 프로그램 - 커서 Cursor (MySQL) (0) | 2020.10.13 |
| [MariaDB] 마리아DB 스토어드 프로그램 - 스토어드 함수 Stored Function (MySQL) (0) | 2020.10.13 |
