Overview
MySQL에서 트리거를 사용하는 경우가 있습니다. 단순 INSERT/UPDATE/DELETE인 경우에는 큰 문제가 없겠지만, "INSERT .. ON DUPLICATE KEY" 또는 "REPLACE INTO"와 같은 특별한 SQL 경우 예상과는 트리거가 다른 동작을 보일 수 가 있습니다. 위 두 쿼리에 따른 트리거 동작에 대해 정리하도록 하겠습니다.Trigger?
트리거는 데이터 변경 쿼리(INSERT/UPDATE/DELETE)가 들어온 경우 이후에 발동되는 이벤트를 의미하며, MySQL에서는 아직까지는 행 기반의 트리거만 동작합니다. 오라클 경우에는 특정 칼럼 변경도 감지하여 트리거를 발동시킬 수는 있습니다만.. ^^일반적으로 SQL이 실행되기 전에 발동되는 "BEFORE"와 SQL 실행 후 발동되는 "AFTER" 트리거가 있습니다. 하단에서는 "INSERT .. ON DUPLICATE KEY" 또는 "REPLACE INTO" 호출 시 각 트리거에 따른 이벤트 발동 순서를 살펴보았습니다.
After Trigger
테스트를 위해 사용한 테이블 스키마와 트리거 생성 구문입니다. test 테이블에 After 트리거를 생성하고 "INSERT ON DUPLICATE KEY UPDATE"과 "REPLACE INTO"를 각각 두 번 수행한 이후 test_log에 기록된 사항을 확인해보겠습니다.-------------------------------------------------------------
## Table Schema
drop table test;
CREATE TABLE `test` (
`i` int(11) NOT NULL,
`j` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`i`)
);
drop table test_log;
CREATE TABLE `test_log` (
`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`t` varchar(10) DEFAULT NULL,
`old` int null,
`new` int null
);
## Triggers
delimiter //
create trigger trg_test_after_insert
after insert on test
for each row
begin
insert into test_log values (now(), 'INSERT', null, new.i);
end;
//
delimiter ;
delimiter //
create trigger trg_test_after_update
after update on test
for each row
begin
insert into test_log values (now(), 'UPDATE', old.i, new.i);
end;
//
delimiter ;
delimiter //
create trigger trg_test_after_delete
after delete on test
for each row
begin
insert into test_log values (now(), 'DELETE', old.i, null);
end;
//
delimiter ;
1) INSERT ON DUPLICATE KEY UPDATE - first time
중복키가 없으므로, 단순 Insert와 동일한 패턴을 보입니다.
-------------------------------------------------------------
kadba@vtom043:test 11:03:38>INSERT INTO test (i,j) VALUES(1,1) ON DUPLICATE KEY UPDATE j=j+1;
Query OK, 1 row affected (0.01 sec)
kadba@vtom043:test 11:03:47>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)
kadba@vtom043:test 11:03:52>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:03:47 | INSERT | NULL | 1 |<==
+---------------------+--------+------+------+
1 row in set (0.00 sec)
2) INSERT ON DUPLICATE KEY UPDATE - second time
INSERT관련된 내용없이 업데이트 트리거만 발동되었습니다. 원하는 형태로 트리거가 동작합니다.
-------------------------------------------------------------
kadba@vtom043:test 11:04:00>INSERT INTO test (i,j) VALUES(1,1) ON DUPLICATE KEY UPDATE j=j+1;
Query OK, 2 rows affected (0.00 sec)
kadba@vtom043:test 11:04:57>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)
kadba@vtom043:test 11:05:16>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:03:47 | INSERT | NULL | 1 |
| 2014-02-04 02:04:57 | UPDATE | 1 | 1 |<==
+---------------------+--------+------+------+
2 rows in set (0.00 sec)
3) REPLACE INTO - first time
이번에는 "REPLACE INTO" 관련 테스트입니다. 위와 마찬가지로 처음에는 충돌나는 키가 없으므로, Innsert와 동일한 패턴을 보입니다.
-------------------------------------------------------------
kadba@vtom043:test 11:07:08>REPLACE INTO test (i,j) VALUES(2,1);
Query OK, 1 row affected (0.00 sec)
kadba@vtom043:test 11:07:15>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
| 2 | 1 |
+---+---+
2 rows in set (0.00 sec)
kadba@vtom043:test 11:07:23>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:03:47 | INSERT | NULL | 1 |
| 2014-02-04 02:04:57 | UPDATE | 1 | 1 |
| 2014-02-04 02:07:15 | INSERT | NULL | 2 |<==
+---------------------+--------+------+------+
3 rows in set (0.00 sec)
4) REPLACE INTO - second time
"REPLACE INTO"는 DELETE->INSERT 하는 방식으로 동작합니다. 트리거 기록에서도 마찬가지로 DELETE->INSERT하는 흔적을 보여줍니다.
-------------------------------------------------------------
kadba@vtom043:test 11:07:27>REPLACE INTO test (i,j) VALUES(2,2);
Query OK, 2 rows affected (0.01 sec)
kadba@vtom043:test 11:08:07>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)
kadba@vtom043:test 11:08:10>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:03:47 | INSERT | NULL | 1 |
| 2014-02-04 02:04:57 | UPDATE | 1 | 1 |
| 2014-02-04 02:07:15 | INSERT | NULL | 2 |
| 2014-02-04 02:08:07 | DELETE | 2 | NULL |<==
| 2014-02-04 02:08:07 | INSERT | NULL | 2 |<==
+---------------------+--------+------+------+
5 rows in set (0.00 sec)
Before Trigger
AFTER 트리거에서는 큰 무리없이 원하는 형태로 트리거가 잘 동작하였습니다. 그런데, BEFORE 트리거에서는 위와는 다르게 동작하게 됩니다. 테이블과 트리거를 아래와 같이 다시 생성합니다.-------------------------------------------------------------
## Table Schema
drop table test;
CREATE TABLE `test` (
`i` int(11) NOT NULL,
`j` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`i`)
);
drop table test_log;
CREATE TABLE `test_log` (
`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`t` varchar(10) DEFAULT NULL,
`old` int null,
`new` int null
);
## Triggers
delimiter //
create trigger trg_test_before_insert
before insert on test
for each row
begin
insert into test_log values (now(), 'INSERT', null, new.i);
end;
//
delimiter ;
delimiter //
create trigger trg_test_before_update
before update on test
for each row
begin
insert into test_log values (now(), 'UPDATE', old.i, new.i);
end;
//
delimiter ;
delimiter //
create trigger trg_test_before_delete
before delete on test
for each row
begin
insert into test_log values (now(), 'DELETE', old.i, null);
end;
//
delimiter ;
1) INSERT ON DUPLICATE KEY UPDATE - first time
AFTER 트리거와 마찬가지로 중복된 키가 없으므로, 무리없이 INSERT트리거가 발동됩니다.
-------------------------------------------------------------
kadba@vtom043:test 11:12:18>INSERT INTO test (i,j) VALUES(1,1) ON DUPLICATE KEY UPDATE j=j+1;
Query OK, 1 row affected (0.00 sec)
kadba@vtom043:test 11:12:19>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 1 |
+---+---+
1 row in set (0.00 sec)
kadba@vtom043:test 11:12:24>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:12:19 | INSERT | NULL | 1 |<==
+---------------------+--------+------+------+
1 row in set (0.00 sec)
2) INSERT ON DUPLICATE KEY UPDATE - second time
문제는 두번째 "DEPLICATE KEY UPDATE"가 발동되는 순간입니다. 이 경우 실제 트리거는 INSERT->UPDATE하는 방식으로 동작합니다.키가 충돌나는 지는 쿼리를 실행하기 전에는 알 수 없기 때문에,INSERT를 시도한 후 중복을 감지하여 UPDATE하는 순서 그대로 트리거가 호출되는 것입니다.
-------------------------------------------------------------
AFTER 트리거와는 다르게 INSERT 이후 UPDATE를 체크합니다.
kadba@vtom043:test 11:12:29>INSERT INTO test (i,j) VALUES(1,1) ON DUPLICATE KEY UPDATE j=j+1;
Query OK, 2 rows affected (0.00 sec)
kadba@vtom043:test 11:12:45>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)
kadba@vtom043:test 11:12:53>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:12:19 | INSERT | NULL | 1 |
| 2014-02-04 02:12:45 | INSERT | NULL | 1 |<==
| 2014-02-04 02:12:45 | UPDATE | 1 | 1 |<==
+---------------------+--------+------+------+
3 rows in set (0.00 sec)
이 경우 복제 테이블을 구성하는 경우 "ON DUPLICATE KEY UPDATE"가 복제 대상에 걸린 PK제약으로 인하여 정상적으로 동작하지 않습니다. 즉! INSERT 실패가 발생하겠죠.
3) REPLACE INTO - first time
REPLACE INTO 또한 중복된 키가 없으므로, 무리없이 데이터가 INSERT됩니다.
-------------------------------------------------------------
kadba@vtom043:test 11:12:55>REPLACE INTO test (i,j) VALUES(2,1);
Query OK, 1 row affected (0.01 sec)
kadba@vtom043:test 11:13:36>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
| 2 | 1 |
+---+---+
2 rows in set (0.00 sec)
kadba@vtom043:test 11:13:43>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:12:19 | INSERT | NULL | 1 |
| 2014-02-04 02:12:45 | INSERT | NULL | 1 |
| 2014-02-04 02:12:45 | UPDATE | 1 | 1 |
| 2014-02-04 02:13:36 | INSERT | NULL | 2 |<==
+---------------------+--------+------+------+
4 rows in set (0.00 sec)
4) REPLACE INTO - second time
REPLACE INTO 또한 중복된 키가 존재하는 경우 문제가 있습니다.마찬가지로 REPLACE INTO 구문이 실행되기 전에는 키가 중복되는 지 알 수 없기 때문에, 먼저 INSERT를 한 이후 DELETE를 하는 방식으로 동작합니다.
-------------------------------------------------------------
INSERT 트리거가 실행된 이후 DELETE가 실행됩니다.
AFTER 트리거와는 반대의 패턴을 보입니다.
kadba@vtom043:test 11:13:45>REPLACE INTO test (i,j) VALUES(2,2);
Query OK, 2 rows affected (0.00 sec)
kadba@vtom043:test 11:14:06>select * from test;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
| 2 | 2 |
+---+---+
2 rows in set (0.00 sec)
kadba@vtom043:test 11:14:10>select * from test_log;
+---------------------+--------+------+------+
| d | t | old | new |
+---------------------+--------+------+------+
| 2014-02-04 02:12:19 | INSERT | NULL | 1 |
| 2014-02-04 02:12:45 | INSERT | NULL | 1 |
| 2014-02-04 02:12:45 | UPDATE | 1 | 1 |
| 2014-02-04 02:13:36 | INSERT | NULL | 2 |
| 2014-02-04 02:14:06 | INSERT | NULL | 2 |<==
| 2014-02-04 02:14:06 | DELETE | 2 | NULL |<==
+---------------------+--------+------+------+
6 rows in set (0.00 sec)
위 결과대로라면, REPLACE INTO 구문을 사용하는 경우에서도 복제 테이블 구성 시 복제 테이블에 걸린 PK제약으로 인하여 INSERT 실패가 날 수 있습니다. 설혹, 실패가 나지 않는다고 하더라도, DELETE 이후 아무런 처리를 하지 않기 때문에, 결과적으로 오히려 복제 테이블에서 데이터를 지워버리는 어이없는 결과가 나오게 됩니다.
Conclusion
AFTER 트리거는 원하는 형태대로 트리거가 제대로 발동되었으나, BEFORE 트리거 경우에는 예상된 순서로 트리거가 발동되지 않습니다. 즉, BEFORE 트리거로 데이터를 처리하는 경우, 게다가 REPLACE 혹은 INSERT ON DUPLICATE UPDATE를 사용하는 경우 반드시 주의가 필요합니다.pt-online-schema-change에서 복제 테이블 구성시 트리거를 사용하는데, 다행히도 AFTER 트리거 방식으로 동작하기 때문에, 결과적으로 스키마 변경에는 아무 문제는 없습니다. ^^
