결론부터 말하면은 대실패 입니다. 의도한대로 전혀 움직이지 않습니다. 그렇기에 MySQL에서는 절대 하지 마십시오.
물론 다른 DBMS인 오라클, MSSQL은 어떤지 아직 직접해보지 않아서 정확히는 모릅니다. 하지만 제가 테스트한 MySQL에서는 전혀 실행이 되지 않습니다.
실패라고 하여도 과정에 대해서 일단 간단히 작성해 놓고 추후에 이런 일이 있으면 교본을 삼으려고 글을 남깁니다.
요구조건
1. INSERT 시에 해당 값을 trigger를 통해서 다른 테이블에 기록
2. 다른테이블 기록은 ‘테이블이름_년월’ 형식에 기록해서 추후에 관리 용이
3. 값이 없는 부분은 가공해서 넣기
어떻게 보면은 정말 간단한 요구사항입니다. trigger의 INSERT시에 캐치해서 월별로 관리하는 Table에 Insert 하는 부분입니다. 물론 이 외에 될수도 있지만 현재로서는 실패 입니다.
DROP TRIGGER IF EXISTS `TRIGGER_TEST`;
DELIMITER //
CREATE TRIGGER `TRIGGER_TEST`
AFTER INSERT ON `TRIGGER_TEST_RAW` FOR EACH ROW
BEGIN
-- 선언
DECLARE Access_Time VARCHAR(20);
DECLARE Ip_From VARCHAR(20);
DECLARE Status_From VARCHAR(20);
DECLARE Work_Rate VARCHAR(10);
DECLARE OrgMessageFrom VARCHAR(80);
-- 값 세팅
SET @Access_Time = IFNULL(NEW.Access_Time, DATE_FORMAT(NOW(), '%Y-%-m-%d %H:%i:%s'));
SET @Ip_From = IFNULL(NEW.Ip_From, '0.0.0.0');
SET @Status_From = IFNULL(NEW.Status_From, 'READY');
SET @Work_Rate = REPLACE(substring_index(NEW.OrgMessageFrom, ",", -1), '%', '');
SET @OrgMessageFrom = NEW.OrgMessageFrom;
-- 쿼리문 조합
SET @TableName = CONCAT('TRIGGER_TEST_DATA_', DATE_FORMAT(NOW(), '%Y%m'));
SET @SQL_A = CONCAT('INSERT INTO `', @TableName, '` (`Access_Time`,`Ip_From`,`Status_From`,`Work_Rate`,`OrgMessageFrom`) VALUES (');
SET @SQL_B = CONCAT(@SQL_A, '\'', IFNULL(@Access_Time, ''), '\'');
SET @SQL_C = CONCAT(@SQL_B, ', \'', IFNULL(@Ip_From, ''), '\'');
SET @SQL_D = CONCAT(@SQL_C, ', \'', IFNULL(@Status_From, ''), '\'');
SET @SQL_E = CONCAT(@SQL_D, ', \'', IFNULL(@Work_Rate, ''), '\'');
SET @SQL_F = CONCAT(@SQL_E, ', \'', IFNULL(@OrgMessageFrom, ''), '\')');
-- 별도 테이블 기록하기
INSERT INTO TRIGGER_TEST_LOG (LOG) VALUES (@SQL_F);
-- 프로시저 호출
CALL DynamicQuery(@SQL_F);
END; //
DELIMITER ;
위에 Trigger 를 통해서 Query문 만들어서 실행해려니 실패해서 프로시저까지 동원하였습니다. Trigger안에서 Query문을 실행할 방법이 없어서 입니다.
우리가 원하는것은 동적테이블이라서 ‘INSERT INTO 테이블’ 만으로 만족을 할수가 없기에 ‘INSERT INTO `테이블_년월`’ 이 되어야 하는것이 포인트 입니다.
그렇기에 별로 Query 문을 만들어서 넘기기로 한것입니다.
DROP PROCEDURE IF EXISTS DynamicQuery;
DELIMITER //
CREATE PROCEDURE DynamicQuery (IN strQuery TEXT)
NOT DETERMINISTIC
BEGIN
SET @SQL = CONCAT(strQuery, ';');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END; //
DELIMITER ;
이제 프로시저도 만들었습니다. 모든것이 끝났다고 생각한 순간에 실행을 하면은 에러가 나옵니다. 아주 간단한 에러 입니다.
/* SQL 오류 (1336): Dynamic SQL is not allowed in stored function or trigger */
위 에러를 뿜어 내면서 그냥 되지 않는다고 하니 그냥 포기해야 될듯 싶습니다. 다른 DBMS에서는 어떻게 될지 아직 해 보지 않았지만 된다면은 이렇게 활용하면은 정말 활용성이 높아지는데 왜 안되는건지 알수가 없습니다.
혹 이런 방법으로 성공하신분이 있다면은 정말 큰 가르침을 내려주시면은 평생 은혜를 잊지 않도록 하겠습니다.