MySQL 5.5 Usage of Trigger for Data Cleansing v1.0
|
12.09.20 |
1.0 |
|
|
|
|
|
|
|
|
|
1. Scenario
기존에 sync_test 라는 이름의 테이블이 있었다.
sync_test 는 다음과 같은 컬럼을 가진다.
sync_test(syncid + vldenddt)
vldenddt 는 datetime 로 default 값으로 ‘9999-12-31 00:00:00’ 이란 값을 가진다.
(* 이하에서 ‘9999’ 라 표현하겠다.)
기존에는 9999 인 값과 아닌 값이 sync_test 에 뒤섞여 있는 상황이었으나
9999 인 값을 sync_test 에,
9999 가 아닌 값을 sync_test_history 에 넣길 원한다.
APP 단의 수정사항은 존재하지 않는다. (들어오는 쿼리형태는 동일하다는 뜻)
APP 단의 수정사항은 존재하지 않기 때문에 DML 은 모두 sync_test 를 바라본다.
추가로 기존 데이터에 대한 초기적재는 여기서 이야기 하지 않겠다.
사실 이 부분은 트리거 구성 후 기존 데이터에 대해
insert into sync_test_history select * from sync_test where vldenddt <> ‘9999-12-31 00:00:00’
위와같이 수행하면 된다.
PK 중복에 대해 걱정이 된다면 IGNORE 옵션을 추가하면 된다.
마지막으로 sync_test 에 대해서 delete 오퍼레이션은 존재하지 않는다고 가정한다.
2. SQL for Test
테스트 초기화 구문
|
drop trigger trig_i_sync_test_vldenddt; drop trigger trig_u_sync_test_vldenddt; drop trigger trig_i_sync_test_history_vldenddt;
truncate table sync_test; truncate table sync_test_history; |
테이블 생성 구문
|
CREATE TABLE `sync_test` ( `syncid` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `vldenddt` datetime NOT NULL DEFAULT '9999-12-31 00:00:00', PRIMARY KEY (`syncid`) USING BTREE);
CREATE TABLE `sync_test_history` ( `syncid` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `vldenddt` datetime NOT NULL DEFAULT '9999-12-31 00:00:00', PRIMARY KEY (`syncid`) USING BTREE); |
3. Conditions for Creating Triggers
DML 은 알다시피 INSERT, UPDATE, DELETE 작업이 있다.
앞서 전제했듯이 DELETE 작업이 sync_test 에 대해 없기에 INSERT, UPDATE 에 대해 데이터를 어떻게 sync_test_history 에 반영할지를 생각하면 된다.
l INSERT
- vldenddt 가 9999 인 데이터가 들어오는 경우
ð sync_test 에 정상적으로 데이터가 입력되면 된다. 부가적인 동작이 필요없다. 굳이 넣는다면 sync_test_history 에 잘못 들어가 있을 데이터 정리를 위해 delete 를 할 수는 있다.
- vldenddt 가 9999 가 아닌 데이터가 들어오는 경우이다.
ð 9999 가 아닌 데이터는 hist 테이블로 들어가야 된다. 이 경우 들어온 데이터를 hist 테이블에 복제하고, 원본 데이터는 삭제해야 한다.
l UPDATE
- vldenddt 가 9999 인 데이터로 수정되는 경우가 있다.
ð 수정된 경우 새로운 데이터를 hist 테이블에 복제하고, 원래의 row 는 삭제한다.
4. DDL for Creating Triggers
두 개의 트리거를 생성한다.
l trig_i_sync_test_vldenddt
Insert 에 대처하기 위한 트리거이다.
9999 가 아닌 데이터 (history로 가야하는) 를 sync_test_history 로 입력한다.
입력 후 트리거를 작동시킨 데이터를 삭제한다. (이 데이터는 9999 가 아닌 데이터로 history 로 가야하는 데이타이다.)
9999 인 데이터가 들어오는 경우는 원본 데이터를 건드릴 부분이 없으나, 복제테이블 (hist) 에 데이터가 잘못 들어가 있는 경우를 대비해 삭제한다.
l trig_u_sync_test_vldenddt
Update 에 대처하기 위한 트리거이다.
이테이블에는 기본적으로 9999 인 데이터들이 모여있다. 업데이트 결과가 9999 가 아니게 된 경우는 history 로 옮겨져야 하며, 이에 대해 데이터를 history 로 복제하고 트리거를 동작시킨 데이터를 삭제한다. 데이터가 9999 인 경우에는 sync_test 에 존재해야 할 데이터로 혹시 모를 history 에 존재할 데이터를 삭제한다.
|
DELIMITER $$ USE `street`$$ DROP TRIGGER /*!50032 IF EXISTS */ `trig_i_sync_test_vldenddt` $$ CREATE TRIGGER `trig_i_sync_test_vldenddt` AFTER INSERT ON `sync_test` FOR EACH ROW BEGIN IF !(@DISABLE_TRIGGERS <=> 1) THEN IF (NEW.vldenddt <> '9999-12-31 00:00:00') THEN INSERT IGNORE sync_test_history ( syncid ,vldenddt ) VALUES ( NEW.syncid ,NEW.vldenddt ); DELETE IGNORE from sync_test where syncid = NEW.syncid; ELSE DELETE IGNORE from sync_test_history where syncid = NEW.syncid; END IF; END IF; END; $$ DELIMITER ;
DELIMITER $$ USE `street`$$ DROP TRIGGER /*!50032 IF EXISTS */ `trig_u_sync_test_vldenddt` $$ CREATE TRIGGER `trig_u_sync_test_vldenddt` AFTER UPDATE ON `sync_test` FOR EACH ROW BEGIN IF !(@DISABLE_TRIGGERS <=> 1) THEN IF (NEW.vldenddt <> '9999-12-31 00:00:00') THEN INSERT IGNORE sync_test_history ( syncid,vldenddt ) VALUES ( NEW.syncid,NEW.vldenddt ); DELETE IGNORE from sync_test where syncid = NEW.syncid; ELSE DELETE IGNORE from sync_test_history where syncid = NEW.syncid; END IF; END IF; END; $$ DELIMITER ; |
트리거 생성 후 다음의 DML 로 내용을 검증해 보았다.
|
case#1 기대되는 효과 이 데이타는 sync_test 에만 있을 것이다.
insert into sync_test(vldenddt) values ('9999-12-31 00:00:00');
case#2 기대되는 효과 이 데이타는 sync_history 에만 있을 것 것이다.
insert into sync_test(vldenddt) values ('2012-12-31 00:00:00');
case#3 기대되는 효과 이 데이타는 sync_test_history 로 이전 될 것이다.
update sync_test set vldenddt = '2012-12-31 00:00:00' where vldenddt ='9999-12-31 00:00:00'; |
case#1
데이터가 sync_test 에만 존재하며 이상없다.
case#2
다음의 에러가 발생하며 table 에 변경은 취소되었다.
ERROR 1442 (HY000): Can't update table 'sync_test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
case#3
다음의 에러가 발생하며 table 에 변경은 취소되었다.
ERROR 1442 (HY000): Can't update table 'sync_test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
위의 1442 에러는 트리거의 sync_test 테이블을 업데이트 할 수 없습니다. 이미 trigger 를 호출한 statement 에 의해 사용되고 있습니다. 란 의미이다.
트리거를 유발한 rows 에 대해 삭제할 수 없는 것으로 추측한다. 실제로도 9999 가 아닌 데이터에 대해 history 테이블에 insert 후 사용하는 DELETE 구문에 대해 주석처리를 했을 때 정상 동작하였다.
5. DDL for Creating Alternative Trigger
4번의 트리거는 DELETE 오퍼레이션에서 실패하였다. 회피할 방법으로는 history 테이블에 데이터가 INSERT 되었을 때 원본 테이블의 데이터를 뒤쳐 삭제하는 방법이다.
|
DELIMITER $$ USE `street`$$ DROP TRIGGER /*!50032 IF EXISTS */ `trig_i_sync_test_vldenddt` $$ CREATE TRIGGER `trig_i_sync_test_vldenddt` AFTER INSERT ON `sync_test` FOR EACH ROW BEGIN IF !(@DISABLE_TRIGGERS <=> 1) THEN IF (NEW.vldenddt <> '9999-12-31 00:00:00') THEN INSERT IGNORE sync_test_history ( syncid ,vldenddt ) VALUES ( NEW.syncid ,NEW.vldenddt ); -- DELETE IGNORE from sync_test where syncid = NEW.syncid; ELSE DELETE IGNORE from sync_test_history where syncid = NEW.syncid; END IF; END IF; END; $$ DELIMITER ;
DELIMITER $$ USE `street`$$ DROP TRIGGER /*!50032 IF EXISTS */ `trig_u_sync_test_vldenddt` $$ CREATE TRIGGER `trig_u_sync_test_vldenddt` AFTER UPDATE ON `sync_test` FOR EACH ROW BEGIN IF !(@DISABLE_TRIGGERS <=> 1) THEN IF (NEW.vldenddt <> '9999-12-31 00:00:00') THEN INSERT IGNORE sync_test_history ( syncid,vldenddt ) VALUES ( NEW.syncid,NEW.vldenddt ); -- DELETE IGNORE from sync_test where syncid = NEW.syncid; ELSE DELETE IGNORE from sync_test_history where syncid = NEW.syncid; END IF; END IF; END; $$ DELIMITER ;
DELIMITER $$ USE `street`$$ DROP TRIGGER /*!50032 IF EXISTS */ `trig_i_sync_test_history_vldenddt` $$ CREATE TRIGGER `trig_i_sync_test_history_vldenddt` AFTER INSERT ON `sync_test_history` FOR EACH ROW BEGIN IF !(@DISABLE_TRIGGERS <=> 1) THEN IF (NEW.vldenddt <> '9999-12-31 00:00:00') THEN DELETE IGNORE from sync_test where syncid = NEW.syncid; END IF; END IF; END; $$ DELIMITER ; |
데이터 초기화 후 트리거를 재생성하였고, 5번의 CASE#1,2,3 를 확인해 보았다.
CASE#1
이상없음
CASE#2
5번과 동일한 에러 발생 (ERROR 1442)
CASE#3
5번과 동일한 에러 발생 (ERROR 1442)
위 결과로 보아 에러를 유발한 부분 (DELETE) 을 제3의 트리거로 분리시키더라도 같이 있을 때와 동일하게 에러가 발생함을 알 수 있었다.
6. Conclusion
Trigger 를 동작시키는 액션이 INSERT, UPDATE 인 경우 그 트리거를 동작시키는 대상 ROW 에 대해 DELETE 수행이 불가능하였다.
다시 말해 시나리오를 DB 만으로 만족시키기 어렵다는 이야기다.
다만 sync_test 안의 데이터가 혼재되어 있는 경우에 한해서는 정기적으로 9999 가 아닌 데이터를 삭제하는 식으로 요건을 충족할 수는 있다.
