[SQL] MySQL 트리거

✍️ Study Note

 

 

 

---

 

트리거 (Trigger)

• 테이블에 삽입, 수정, 삭제 등의 작업(이벤트) 발생 시, 자동으로 작동하는 개체.
• 데이터 무결성에 도움
• 직접 실행시키지 못하고, 테이블에 이벤트가 발생할 때 자동으로 실행

 

 

AFTER 트리거 생성

DELIMITER //				-- 트리거 생성
CREATE TRIGGER TESTTRG			-- 트리거 작동조건
    AFTER DELETE
    ON TESTTBL22			-- TESTTBL22 테이블에 이벤트 발생 시
    FOR EACH ROW
BEGIN 
    SET @MSG = '가수 그룹이 삭제됨';	-- 트리거로 발생하는 작업
END //
DELIMITER ;



SET @MSG = '';
INSERT INTO TESTTBL22 VALUES(4,'마마무');	-- 트리거 X
SELECT @MSG;

UPDATE TESTTBL22 SET TXT = '블핑' WHERE ID =3;	-- 트리거 X
SELECT @MSG;

DELETE FROM TESTTBL22 WHERE ID =4;		-- 트리거 O
SELECT @MSG;

 

1. UPDATE / DELETE + AFTER TRIGGER 예시

DELIMITER // 
CREATE TRIGGER BACKUPUSERTBL_UPDATETRG
    AFTER UPDATE		-- 수정 시 트리거 작동
    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 ;
    
    
DELIMITER //
CREATE TRIGGER BACKUSERTBL_DELTETRG
    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 USERTBL SET ADDR = '몽고' WHERE USERID = 'JKW'; 
DELETE FROM USERTBL WHERE HEIGHT >=177;
SELECT * FROM BACKUP_USERTBL;	-- 트리거 작동됨

TRUNCATE TABLE USERTBL;
SELECT * FROM BACKUP_USERTBL; -- 트리거 작동안됨. DELETE문이 아니기에

 

TRUNCATE는 DELETE TRIGGER 작동시키지 않음.

 

2. INSERT + AFTER TRIGGER 예시

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', 'A');

 

 

BEFORE 트리거 생성

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','B'),
('BBB', '비이', 2977, '경기', '011', '1113333', 171, '2019-03-25','A');

SELECT * FROM USERTBL; -- 1번은 0, 2번은 현재년도 입력된것을 확인할 수 있음

 

 

트리거가 생성하는 임시 테이블

NEW

• INSERT, UPDATE 작업 시, 변경할 새로운 데이터를 잠시 저장하는 테이블.
• NEW에 저장되었다가 '작업 테이블'에 값이 입력, 변경된다

OLD 

• DELETE, UPDATE 작업 시, 삭제 또는 변경되기 전의 예전 값이 저장되는 테이블

 

 

트리거 조회, 삭제

SHOW TRIGGERS FROM SQLDB;
DROP TRIGGER USERTBL_INSERTTRG;

 

 

기타 트리거

1. 다중 트리거 : 하나의 테이블에 동일한 트리거 여러 개 부착
2. 중첩 트리거 : 트리거가 또 다른 트리거를 작동하는 것

 

중첩트리거 예시

제품 주문이 들어오면 (ORDERTBL), 제품 수가 줄어들고 (PRODTBL) 배송 내역이 저장되도록 (DELIVERTBL)

-- 준비#1 테이블 생성
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);
  
-- 준비#2 데이터 입력
INSERT INTO PRODTBL VALUES ('사과', 100), ('귤', 100), ('배', 100);

-- 트리거(ORDER -> PROD) 생성
DELIMITER //
CREATE TRIGGER ORDERTRG 
    AFTER INSERT
    ON ORDERTBL		-- ORDERTBL에 주문이 들어올 경우
    FOR EACH ROW
BEGIN 
    UPDATE PRODTBL SET ACCOUNT = ACCOUNT - NEW.ORDERAMOUNT	-- PRODTBL의 상품개수가 줄어든다
    WHERE PRODNAME = NEW.PRODNAME;
END //
DEILMITER ;

-- 트리거(PROD -> DELIVER) 생성
DELIMITER //
CREATE TRIGGER PRODTRG
    AFTER UPDATE
    ON PRODTBL	-- PRODTBL에 업데이트가 있을 경우
    FOR EACH ROW
BEGIN 
    DECLARE ORDERAMOUNT INT;
    SET ORDERAMOUNT = OLD.ACCOUNT - NEW.ACCOUNT; -- PRODTBL의 OLD, NEW 값 이용해서 주문수량 확인
    INSERT INTO DELIVERTBL (PRODNAME, ACCOUNT)
    VALUES(NEW.PRODNAME, ORDERAMOUNT);	-- DELIVERTBL에 새로운 수량 업데이트
END//
DELIMITER ;

-- 테스트 (데이터입력)
INSERT INTO ORDERTBL VALUES (NULL, 'JOHN', '배', 5);

SELECT * FROM ORDERTBL;
SELECT * FROM PRODTBL;
SELECT * FROM DELIVERTBL;