출처 | https://daegwonkim.tistory.com/181
트리거
트리거(trigger)는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해준다. 즉 트리거를 사용하면 데이터에 오류가 발생하는 것을 막을 수 있다. 이런 것을 데이터의 무결성이라고 부르기도 한다.
트리거는 테이블에 DML(Data Manipulation Language) 문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다. 테이블에 미리 부착(attach)되는 프로그램 코드라고 생각하면 된다.
트리거는 스토어드 프로시저와 문법이 비슷하지만, CALL 문으로 직접 실행시킬 수는 없고 오직 테이블에 INSERT, UPDATE, DELETE 등의 이벤트가 발생할 경우에만 자동으로 실행된다. 또한 스토어드 프로시저와 달리 트리거에는 IN, OUT 매개변수를 사용할 수 없다.
[형식]
CREATE TRIGGER [트리거명]
[BEFORE/AFTER 트리거 실행 시점 속성설정]
ON [트리거 설정 테이블명]
FOR EACH ROW
BEGIN
[트리거 실행 내용]
END[예제]
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;
AFTER 트리거의 사용
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
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 ;
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, -- ex: 이승기 정보를 삭제하면 '이후에' 실행한다.
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;
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');-
백업 테이블을 생성
-
DELIMITER : 구분문자. 뒤에 //가 나오면 기존의 세미콜론(;)을 //로 대신한다. 이는 CREATE PROCEDURE ~~ END까지를 하나의 단락으로 묶어주는 효과를 갖는다. 그리고 제일 마지막 행에서 세미콜론(;)으로 돌려놓아야 한다.
-
OLD : 기존(트리거를 부착한 테이블)
-
NEW : 신규(백업테이블)
- 위처럼 하면 회원테이블을 삭제하면 백업테이블에 삭제한 행이 쌓이는 것을 알 수 있다
트리거가 생성하는 임시 테이블
-
NEW테이블 : insert, update 작업 시 변경할 새로운 데이터를 잠깐 저장 후 테이블에 입력/변경한다. 그러므로 NEW테이블을 조작하면 입력되는 새로운 값을 다른 값으로 대치할 수 있음.
-
OLD테이블 : delete, update 작업이 수행되면서 삭제 또는 변경되기 전의 예전 값이 잠깐 저장된다. 예전 데이터를 참조하기 위해서는 OLD 테이블을 참조하면 된다
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');
SHOW TRIGGERS FROM sqlDB;
DROP TRIGGER userTbl_BeforeInsertTrg;- BEFORE 트리거 : 입력되는 값이 들어 있는 NEW 테이블의 값을 검사해서 1900미만인 경우 아예0으로, 현재 연도보다 초과라면 현재연도로 바꾼다.
다중트리거
: 하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것. 예로 AFTER INSERT 트리거가 한 개 테이블에 2개 이상 부착되어 있을 수도 있다.
중첩트리거
: 트리거가 또 다른 트리거를 작동하는 것. 물건을 구매하면, 물품테이블에서 남은 개수를 감소시키고, 배송 테이블 건수 입력
MySQL은 현재 중첩트리거는 지원하지만 재귀트리거는 지원하지 않는다. 다른 DBMS는 재귀 트리거를 지원하기도 한다.
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;
SELECT * FROM orderTbl;
SELECT * FROM prodTbl;
SELECT * FROM deliverTbl;ALTER TABLE deliverTBL CHANGE prodName productName VARCHAR(5);
ALTER TABLE deliverTBL CHANGE prodName productName VARCHAR(5);
INSERT INTO orderTbl VALUES (NULL, 'DANG', '사과', 9);
SELECT * FROM orderTbl;
SELECT * FROM prodTbl;
SELECT * FROM deliverTbl;
아는만큼보인다.