파트 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이다. 다음과 같이 작동한다. 그림1

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개 이상 부착되어 있을 수도 있다.

중첩 트리거

중첩 트리거란 트리거가 또 다른 트리거를 작동하는 것을 말한다. 그림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 트리거이름’을 지정하면 지정한 트리거가 작동하기 이전에 현재 트리거가 작동한다.



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony