MySql 의 Trigger(트리거) 로 정보 업데이트

MySql 의 Trigger(트리거) 로 정보 업데이트

 

개발환경 : 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; 를 사용하면 된다.

IF NEW.count < 0 THEN
   SET NEW.count = 0;
ELSEIF NEW.count > 100 THEN
   SET NEW.count = 100;
END IF;

이번에는 변수를 설정하고 값을 셋팅하는 방법이다.

두가지가 있는데 SET 을 사용하는 방법과 DECLARE 가 있다. 사용법은 아래

예제를 참고 하기 바란다.

DELIMITER //
DROP TRIGGER IF EXISTS bar //
CREATE TRIGGER bar AFTER INSERT ON foo
FOR EACH ROW BEGIN
  DECLARE x INT;
  SET x = NEW.i;
  SET @a = x; -- set user variable outside trigger
END//

DELIMITER ;

SET @a = 0;
SELECT @a; -- returns 0
INSERT INTO foo () VALUES ();
SELECT @a; -- returns 1, the value it got during the trigger

그리고 정해진 값을 셋팅하는 것이 아니라 기존에 있는 DB 에서

데이터를 불러와서 셋팅할 때 이다

CREATE TRIGGER bar AFTER INSERT ON foo
FOR 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 문법과 조합해서 검색한 결과를 비교해서 로직을 적용하는

예제이다.

CREATE TRIGGER clearcamcdr AFTER INSERT ON `asteriskcdrdb`.`cdr` 
FOR EACH ROW
BEGIN
  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 테이블에서 업데이트 되는 행의 값을 나타낸다.

그 값의 조건을 비교해 로직을 처리하고 있는 것이다.

DROP TRIGGER IF EXISTS `sport`.`tr_update_gameresult`;

CREATE TRIGGER `sport`.`tr_update_gameresult` BEFORE UPDATE ON sport.tb_game 
FOR EACH ROW
BEGIN
  -- 게임이 종료 되었는지 판단
  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 토드에서 트리거를 작성한 화면이다.

작성한 트리거 리스트에 대한 정보 이다

새로운 트리거를 작성하기 위한 화면이다