MySQL 4: Trigger, Event Scheduler

Trigger 은 테이블에 무슨 일이 일어나면 자동으로 실행되는 작업이다. 테이블에 삽입, 수정, 삭제 등의 작업(이벤트) 발생 시에 자동으로 작동되는 개체로, Stored Procedure 과 비슷한 모양을 가진다. 트리거로 많이 사용되는 예로는, 데이터 삭제 시에 사용되는 트리거이다. 만약 누군가가 어느 테이블의 행을 고의 또는 실수로 삭제한다면, 삭제된 행의 내용을 복구하는 것도 어려우며 누가 지웠는지 추적하는 것도 어렵다. 이러한 경우 A 테이블에서 행이 삭제되는 순간에 삭제된 행의 내용, 시간, 삭제한 사용자 등을 다른 테이블에 기록해 둠으로써 이를 해결할 수 있다.

 

 

트리커는 제약조건들과 더불어 데이터 무결성을 위해 MySQL 에서 활용할 수 있는 또다른 기능이다. 트리거는 스토어드 프로시저와 작동이 비슷하지만, 직접 실행시킬 수는 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다. 그리고 스토어드 프로시저와 달리 IN, OUT 도 사용할 수 없다. 우선 아래와 같은 간단한 트리거 예제를 실행해본다.

 

 

USE sqldb;
CREATE TABLE IF NOT EXISTS testTrigger(id INT, txt VARCHAR(10));
INSERT INTO testTrigger VALUES(1, '르세라핌');
INSERT INTO testTrigger VALUES(2, '아이브');
INSERT INTO testTrigger VALUES(3, '뉴진스');

 

 

CREATE TRIGGER testTrg
	AFTER DELETE 
	ON testTrigger
	FOR EACH ROW 
BEGIN 
	SET @msg = '가수 그룹이 삭제됨';
END

SET @msg = '';
INSERT INTO testTrigger VALUES(4, '마마무');
SELECT @msg;
UPDATE testTrigger SET txt='블핑' WHERE id = 4;
SELECT @msg;
DELETE FROM testTrigger WHERE id = 4;
SELECT @msg;

 

 

result1

 

result2

 

result3

 

 

 

트리거는 AFTER 트리거와 BEFORE 트리거로 구분될 수 있다. AFTER 트리거는 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거로 이름처럼 작업 후에 작동한다. BEFORE 은 작업 전에 작동하는 것 외에는 AFTER 과 동일하다. 또 트리거에는 trigger-order 이라는 것을 지정해줄 수 있다. 이는 테이블에 여러 개의 트리거가 부착되어 있을 때 트리거의 순서를 지정해줄 수 있는 것이다. 

 

 

우선 AFTER 트리거를 활용하여 맨처음에 예시로 언급한 백업 데이터를 저장하는 것을 작성해본다.

 

 

USE sqldb;
DROP TABLE IF EXISTS backup_userTbl;
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(6),
	modDate DATE,
	modUser VARCHAR(256)
);

 

 

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,
	'UPDATE', CURDATE(), CURRENT_USER()
	);
END

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,
	'DELETE', CURDATE(), CURRENT_USER()
	);
END


UPDATE userTbl SET addr = '몽고' WHERE userID = 'JKW';
DELETE FROM userTbl WHERE userID = 'YJS';

SELECT * FROM backup_userTbl

 

 

result

 

 

 

트리거에서 작업이 수행되면 임시로 사용되는 시스템 테이블이 두 개 있다. OLD, NEW 이다. NEW 테이블은 INSERT, UPDATE 작업 시에 변경할 새로운 데이터를 잠깐 저장해둔다. 해당 테이블에 INSERT 혹은 UPDATE 명령이 수행되면 입력/변경될 새 값이 NEW 테이블에 먼저 저장된 후에 NEW 테이블의 값을 테이블에 입력/변경하는 것이다. NEW 테이블을 조작하면 입력되는 새로운 값을 다른 값으로 대치시킬 수 있다. 예를 들어, BEFORE 트리거를 사용할 때, 들어오는 값이 잘못 설정되어 있는지 확인하고 정말 잘못되어 있다면 NEW 테이블의 데이터를 수정하여 저장하게끔 할 수 있다. OLD 테이블은 DELETE, UPDATE 작업이 수행되면 삭제 또는 변경 이전의 값이 저장된다. 위 예시에서 사용된 것을 확인할 수 있다.

 

 

 

다중 트리거, 중첩 트리거에 대해서도 알아본다. 다중 트리거는 한 테이블에 트리거가 여러 개 부착되어 있는 것을 말한다. 다중 트리거를 사용할 때는, 앞서 언급한 것처럼 트리거의 작동 순서를 지정해줄 수 있다. 아래와 같은 형식이다.

 

 

{ FOLLOWS | PRECEDES } other_trigger_name

 

 

'FOLLOWS 트리거이름' 을 지정하면 지정한 트리거 다음에 현재 트리거가 작동되고, 'PRECEDES 트리거이름' 을 지정하면 지정한 트리거 이전에 현재 트리거가 작동된다.

 

 

 

중첩 트리거는 트리거가 또 다른 트리거를 작동하는 것을 말한다. 예를 들어, 첫 번째 트리거에서 테이블 A 의 값을 DELETE 시킨다면, 테이블 A 에 AFTER TRIGGER (DELETE) 가 걸려있다면 또 다른 트리거가 발동될 것이다. 이러한 중첩 트리거는 때로는 시스템 성능에 좋지 않은 영향을 미치기도 한다. 뒤의 트리거 작업이 실패하게 되면 앞선 작업들이 전부 ROLLBACK 되기 때문이다. (MySQL 에서는 재귀 트리거는 지원하지 않는다)

 

 

 

추가적으로 Event Scheduler 에 대해서도 알아보았다. 이는 SQL 선언을 포함하는 객체로 데이터베이스에 저장되어 1회 또는 설정한 기간마다 반복해서 실행될 수 있다. 예를 들어, 어떠한 게시판에서 포스팅 시간을 예약을 걸어두고 올려둘 수 있다. 이 때, 이를 활용하여 시간에 맞춰 등록되게끔 하는 것이다. MySQL 은 scheduled event 를 실행하기 위해 event scheduler thread 라는 특별한 스레드를 사용한다. 이 스레드는 백그라운드에서 실행되며, 지정된 시간에 이벤트를 트리거하여 SQL 문을 실행하거나 다른 작업을 수행하는 것이다.

 

 

 

반복되는 작업에 대해서 이를 작성할 때는 아래와 같이 작성한다.

 

 

DROP EVENT IF EXISTS eventTest;
CREATE EVENT eventTest
ON SCHEDULE EVERY 1 MINUTE  
STARTS CURRENT_TIMESTAMP 
DO
	INSERT INTO usertbl VALUES ('TST', '테스트', 2023, '서울', null, null, 188, CURDATE(), null);

select * from usertbl u

 

 

result

 

 

위 이벤트는 CURRENT_TIMESTAMP 부터 시작하여 매 분마다 DO 밑의 쿼리를 반복하여 실행한다. 잘 실행된다.

 

 

 

반복되지 않고 1 회만 실행할 때, 시간을 지정해서 실행하는 방법에 대해서도 알아보았다. 아래와 같이 작성한다.

 

 

DROP EVENT IF EXISTS eventTest2;
CREATE EVENT eventTest2
ON SCHEDULE AT CURRENT_TIMESTAMP  + INTERVAL 1 MINUTE
ON COMPLETION NOT PRESERVE
DO
	INSERT INTO usertbl VALUES ('TTT', '테스트', 2023, '서울', null, null, 188, CURDATE(), null);

SELECT * FROM usertbl u

 

 

result

 

 

이 떄에는 EVERY 를 쓰지 않고 'AT 시간' 을 작성한다. 위 이벤트는 지금 시간을 기준으로 1 분 뒤에 실행되게끔 한 것이다. ON COMPLETION NOT PRESERVE 는 이벤트가 실행된 뒤 이벤트를 삭제하게끔 한다. (NOT 을 없애면 이벤트가 실행된 뒤에도 남아있다)

 

 

이렇게 작성한 트리거, 이벤트에 대한 정보는 아래와 같이 확인해볼 수 있다.

 

 

SHOW TRIGGERS;

 

 

Trigger

 

 

SELECT * FROM information_schema.events;

 

 

Event

 

 

 

 

 

 

 

 

 

Reference:

이것이 MySQL 이다