[MariaDB] 마리아DB 스토어드 프로그램 - 트리거 Trigger (MySQL)

[MariaDB] 마리아DB 스토어드 프로그램 - 트리거 Trigger (MySQL)

 

스토어드 프로그램 종류는 4가지 입니다.

1. 스토어드 프로시저 Stored Procedure
2. 스토어드 함수 Stored Function
3. 커서 Cursor
4. 트리거 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 트리거명;	-- '트리거명' 트리거를 삭제

 

 

중첩트리거 활용하기

활용 시나리오

1. 구매 테이블, 물품 테이블, 배송 테이블 3개의 테이블이 존재합니다.
2. 고객이 물건을 구매 시 구매테이블에 값이 INSERT 됩니다.
3. 구매테이블에 값이 INSERT 될 경우 -> 트리거로 물품테이블의 물품 남은 갯수를 수정시킵니다.(UPDATE)
4. 물품테이블의 값이 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] 전체보기

 

'MariaDB' 카테고리의 글 목록