[MySQL] 테이블 데이터 감지(트리거) 사용하는 방법
트리거란? RDBMS에서 트리거(trigger)는 데이터베이스에서 발생하는 특정 이벤트(INSERT, UPDATE, DELETE 등)에 반응하여 자동으로 실행되는 SQL 코드 블록을 의미합니다. 트리거는 일반적으로 데이터 유효성 검사, 데이터 변경 로깅 등의 작업에 사용됩니다.
MySQL 트리거 테스트
트리거든 어떤 것이든 버전에 따라 사용방법은 약간 다를 수 있는점 감안하고 보시면 됩니다. 아래 많이 사용하는 입력, 수정, 삭제에 대한 데이터를 감지하여 특정한 쿼리를 실행하는 예제입니다.
1) 트리거 테스트용 테이블 생성
CREATE TABLE tbl_data (
id int comment 'pk',
memo1 tinytext not null comment 'text',
indate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
primary key(id)
) comment 'trigger test data - 트리거로 감지할 테이블';
INSERT INTO test.tbl_data (id, memo1) values ( 1, 'memo1' );
INSERT INTO test.tbl_data (id, memo1) values ( 2, 'memo2' );
select * from test.tbl_data;
CREATE TABLE test_tr (
id int comment 'pk',
memo1 tinytext not null comment 'text',
indate datetime not null comment 'indate',
primary key(id)
) comment 'trigger test - 트리거를 통해 입력 삭제 되는 테이블';
SELECT * FROM test.test_tr;
2) 트리거 생성
아래와 같이 오토커밋일 경우 쿼리가 실행이 되는 것이지 완전하게 보장하는 것은 아니다.
즉, 오래 사용하다 보면 본인이 생각한 데이터와 다를 수 있다는 것이다.
그것에 대한 이유는 다양할 수 있으니 별도 생각해 봐야 한다.
# 필요할 경우 호스트명 지정
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER tr_insert_test1 AFTER INSERT ON test.tbl_data
FOR EACH ROW BEGIN
IF @@hostname='db-master' THEN
DELETE FROM test.test_tr WHERE id=NEW.id;
INSERT INTO test.test_tr SELECT id, 'insert', now() FROM test.test WHERE id=NEW.id;
END IF;
END $$
DELIMITER;
FOR EACH ROW BEGIN ... END; 부분은 실제로 실행되는 SQL 문장들을 포함하고 있습니다.
여기서 'NEW'라고 지칭된 것은 삽입된 새로운 레코드를 가르킵니다.
# INSERT TRIGGERS
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER tr_insert_test AFTER INSERT ON test.tbl_data
FOR EACH ROW BEGIN
DELETE FROM test.test_tr WHERE id=NEW.id;
INSERT INTO test.test_tr SELECT id FROM test.tbl_data WHERE id=NEW.id;
END $$
DELIMITER;
# UPDATE TRIGGERS
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER tr_update_test AFTER UPDATE ON test.tbl_data
FOR EACH ROW BEGIN
DELETE FROM test.test_tr WHERE id=NEW.id;
INSERT INTO test.test_tr SELECT id, 'update', now() FROM test.tbl_data WHERE id=NEW.id;
END IF;
END $$
DELIMITER;
# DELETE TRIGGERS
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER tr_delete_test AFTER DELETE ON test.tbl_data
FOR EACH ROW BEGIN
DELETE FROM test.test_tr WHERE id=OLD.id;
END $$
DELIMITER;
# 현재 목록 보기
show triggers \G
3) 데이블 감지가 잘 되는지 확인해 본다.
INSERT INTO test.tbl_data (id, memo1) values ( 2, 'memo2' );
# 위 인서트네 대한 데이터가 입력 되었는지 확인해본다.
select * from test.test_tr;
