개발환경 : MySQL 5.2 , window XP, Toad MySQL 4.6 Freeware |
l 트리거를 사용하기 위한 조건으로 MySql 5.0.17 이상이어야 한다.
특정 테이블의 값이 변경되었을 때 관련 정보테이블을 변경해야 되는
경우가 있다. 내가 이글을 쓰게 된 것은 스포츠 경기 결과 업데이트를
해야 되는 경우가 생겼는데 batch job 으로 처리 하는 것 보다
업데이트 시 DBMS 내부에서 관련 테이블 들을 처리하는 것이
더 효율적이라고 생각했기 때문이다.
Web application 에서 처리하게 된다면 업데이트 값을 매번 체크해야되고
체크된 값을 업데이트 할 때 시스템에 상당한 부하를 주게 된다. Tirgger 를
사용하게 되면 WAS 에 상당한 부하와 소스를 간결하게 만들수 있다.
복잡한 로직을 트리거로 만들게 되면 업무파악과 유지보수가 까다로우므로
적절하게 적용해야 될 것 이다.
트리거를 작성하기 위해 문법적인 요소와 몇가지 구문예제를
차례대로 언급하였다.
여러 개의 트리거 구문을 같이 집어 넣을 때 구문을 구분할 SQL 단위 구분자가
필요하다. 그것이 DELIMITER $ / DELIMITER; 이다.
FOR EACH ROW BEGIN 은 프로그램에서 for 구문이라고 생각하면된다.
업데이트 된 행이 여러 개 일 때 ROW 별로 하나씩 모두 실행하겠다는
것이다.
트리거 구문내에서 사용할수 있는 별칭에 대해서 알아본다.
테이블이 변경되는 경우는 두가지가 있는데 INSERT 와 UPDATE 가 있다.
INSERT 로 테이블의 값이 변경되었다면 NSERT INTO 테이블 (필드1,필드2) VALUES (값1, 값2)
형태일 것이다.여기에서 입력된 값1 과 값2 는 NEW.필드명 으로 값을
가져와서 사용이 가능하다.
UPDATE 로 변경된 테이블일 경우 새로 입력된 값은 NEW.필드명 이 되며
업데이트 되기면 값은 OLD.필드명이 된다. 이렇게 수행된 테이블의 값을
받아서 관련 테이블들의 정보를 변경해 주면 된다.
다음은 구문에 관한 예제인데 아래는 IF 문 예제이다.
트리거 관련 문법이라기 보다 문장을 작성하기 위한 SQL 문법이라고 보면된다.
몇가지 경우의 예제를 나열 하였으므로 참고 하기 바란다.
값의 변화에 따른 로직적용을 위한 IF 문은 아래
예제와 같이 IF ~ THEN ~ ELSEIF ~ END IF; 를 사용하면 된다.
1 2 3 4 5 | IF NEW.count < 0 THEN SET NEW.count = 0;ELSEIF NEW.count > 100 THEN SET NEW.count = 100;END IF; |
이번에는 변수를 설정하고 값을 셋팅하는 방법이다.
두가지가 있는데 SET 을 사용하는 방법과 DECLARE 가 있다. 사용법은 아래
예제를 참고 하기 바란다.1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DELIMITER //DROP TRIGGER IF EXISTS bar //CREATE TRIGGER bar AFTER INSERT ON fooFOR EACH ROW BEGIN DECLARE x INT; SET x = NEW.i; SET @a = x; -- set user variable outside triggerEND//DELIMITER ;SET @a = 0;SELECT @a; -- returns 0INSERT INTO foo () VALUES ();SELECT @a; -- returns 1, the value it got during the trigger |
그리고 정해진 값을 셋팅하는 것이 아니라 기존에 있는 DB 에서
데이터를 불러와서 셋팅할 때 이다1 2 3 4 5 6 | CREATE TRIGGER bar AFTER INSERT ON fooFOR EACH ROW BEGIN DECLARE x INT; SET x = (SELECT MAX(age) FROM users WHERE name = 'Bill'); -- OK even when more than one row with 'Bill'END// |
IF 문법과 조합해서 검색한 결과를 비교해서 로직을 적용하는
예제이다.1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TRIGGER clearcamcdr AFTER INSERT ON `asteriskcdrdb`.`cdr` FOR EACH ROWBEGIN SET @INC = (SELECT sip_inc FROM trunks LIMIT 1); IF NEW.billsec >1 AND NEW.channel LIKE @INC AND NEW.dstchannel NOT LIKE "" THEN insert into `asteriskcdrdb`.`filtre` (id_appel,date_appel,source,destinataire,duree,sens,commentaire,suivi) values (NEW.id,NEW.calldate,NEW.src,NEW.dstchannel,NEW.billsec,"entrant","",""); END IF;END$$ |
이번에 시스템에 적용한 트리거의 내용이다.
NEW 가 붙은 필드들은 tb_game 테이블에서 업데이트 되는 행의 값을 나타낸다.
그 값의 조건을 비교해 로직을 처리하고 있는 것이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DROP TRIGGER IF EXISTS `sport`.`tr_update_gameresult`;CREATE TRIGGER `sport`.`tr_update_gameresult` BEFORE UPDATE ON sport.tb_game FOR EACH ROWBEGIN -- 게임이 종료 되었는지 판단 IF NEW.COMP_CODE = '3' THEN SET @exception = (select EXPECATE from tb_reg_game where GAME_NO = NEW.GAME_NO); IF @exception = NEW.VIC_CODE THEN update tb_reg_game set `HIT_RESULT` = '1' where GAME_NO = NEW.GAME_NO; ELSE update tb_reg_game set `HIT_RESULT` = '2' where GAME_NO = NEW.GAME_NO; END IF; END IF;END; |
참고로 MySQL 토드에서 트리거를 작성한 화면이다.
작성한 트리거 리스트에 대한 정보 이다
새로운 트리거를 작성하기 위한 화면이다
