- 규약
  INSERT : NEW.컬럼명
  UPDATE : NEW.컬럼명, OLD.컬럼명
  DELETE : OLD.컬럼명

 - 생성 방식   
 AFTER INSERT : INSERT 처리된후
 BEFORE INSERT : INSERT 되기전


- Trigger 생성(ReminderOnePlus)

* 아래와 똑같이 해줘야함 //도 넣어줘야 한다.

 mysql> delimiter //
 mysql> create trigger ReminderOnePlus after update on sms_tbl
    --> for each row
    --> BEGIN
    -->  IF NEW.status = 'F' then
    -->   UPDATE user_tbl
    -->       SET REMINDER = REMINDER + 1 
    -->        WHERE ID=OLD.reg_user AND CAPACITY > REMINDER;
    -->  END IF;
    --> END; //
 mysql> delimiter;

 

* 빨간색으로 된 부분을 해당 Logic에 맞게 변경하여 사용하면 된다.

 

- 설명

   이름은 임의로 ReminderOnePlus라고 생성.

    Database sms_tbl 테이블의 status column이 F로 update되었을 경우

    Database user_tbl 테이블의 REMINDER column을 +1 하는 쿼리를 실행한다.

    after update 대신 before update, before inert, atfer inert 를 사용할 수 있다.

 

- 생성 확인

  mysql> show triggers;

 


----------------------------------------------------------------------------------------------------


여러 개의 트리거 구문을 같이 집어 넣을 때 구문을 구분할 SQL 단위 구분자가

필요하다그것이 DELIMITER $ / DELIMITER; 이다.

 

FOR EACH ROW BEGIN 은 프로그램에서 for 구문이라고 생각하면된다.

업데이트 된 행이 여러 개 일 때 ROW 별로 하나씩 모두 실행하겠다는

것이다.

 

트리거 구문내에서 사용할수 있는 별칭에 대해서 알아본다.

테이블이 변경되는 경우는 두가지가 있는데 INSERT 와 UPDATE 가 있다.

INSERT 로 테이블의 값이 변경되었다면 NSERT INTO 테이블 (필드1,필드2) VALUES (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 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 에서

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

1
2
3
4
5
6
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 문법과 조합해서 검색한 결과를 비교해서 로직을 적용하는

예제이다.

1
2
3
4
5
6
7
8
9
10
11
12
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 테이블에서 업데이트 되는 행의 값을 나타낸다.

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

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 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;






DELIMITER $$


USE `visa`$$


DROP TRIGGER /*!50032 IF EXISTS */ `visa_code_insert`$$


CREATE

    /*!50017 DEFINER = 'pd'@'localhost' */

    TRIGGER `visa_code_insert` AFTER INSERT ON `nation_info` 

    FOR EACH ROW BEGIN

/* 비자 발급 정보 */

INSERT INTO visa_info   

( nation_code ) 

VALUES 

( new.code );

/* 비자 발급처 정보 */

INSERT INTO visa_issue    

( nation_code ) 

VALUES 

( new.code );

    END;

$$


DELIMITER ;


----------------------------------------------------------------------------------------------


DELIMITER $$


USE `bank`$$


DROP TRIGGER /*!50032 IF EXISTS */ `bank_currency_info_latest_branch_delete`$$


CREATE

    /*!50017 DEFINER = 'pd'@'localhost' */

    TRIGGER `bank_currency_info_latest_branch_delete` AFTER DELETE ON `bank_currency_info_latest_branch` 

    FOR EACH ROW BEGIN 

INSERT INTO bank_currency_info_latest_branch_history 

( bank_code, branch_code, ROW, data_type, cu_code, cash_buy, cash_sell, b_exchn_rate, percent_cash_buy, percent_cash_sell, bank_choice_ranking, delivery_bank_code, delivery_commission, delivery_is_use, moddate, moduser, history_date, history_type ) 

VALUES 

( old.bank_code, old.branch_code, old.row, old.data_type, old.cu_code, old.cash_buy, old.cash_sell, old.b_exchn_rate, old.percent_cash_buy, old.percent_cash_sell, old.bank_choice_ranking, old.delivery_bank_code, old.delivery_commission, old.delivery_is_use, old.moddate, old.moduser, NOW(), 'delete' );

    END;

$$


DELIMITER ;


----------------------------------------------------------------------------------------------


DELIMITER $$


USE `bank`$$


DROP TRIGGER /*!50032 IF EXISTS */ `bank_currency_info_latest_branch_insert`$$


CREATE

    /*!50017 DEFINER = 'pd'@'localhost' */

    TRIGGER `bank_currency_info_latest_branch_insert` AFTER INSERT ON `bank_currency_info_latest_branch` 

    FOR EACH ROW BEGIN 

INSERT INTO bank_currency_info_latest_branch_history 

( bank_code, branch_code, ROW, data_type, cu_code, cash_buy, cash_sell, b_exchn_rate, percent_cash_buy, percent_cash_sell, bank_choice_ranking, delivery_bank_code, delivery_commission, delivery_is_use, moddate, moduser, history_date, history_type ) 

VALUES 

( new.bank_code, new.branch_code, new.row, new.data_type, new.cu_code, new.cash_buy, new.cash_sell, new.b_exchn_rate, new.percent_cash_buy, new.percent_cash_sell, new.bank_choice_ranking, new.delivery_bank_code, new.delivery_commission, new.delivery_is_use, new.moddate, new.moduser, NOW(), 'insert' );

    END;

$$


DELIMITER ;


----------------------------------------------------------------------------------------------


DELIMITER $$


USE `bank`$$


DROP TRIGGER /*!50032 IF EXISTS */ `bank_currency_info_latest_branch_update`$$


CREATE

    /*!50017 DEFINER = 'pd'@'localhost' */

    TRIGGER `bank_currency_info_latest_branch_update` AFTER UPDATE ON `bank_currency_info_latest_branch` 

    FOR EACH ROW BEGIN 

INSERT INTO bank_currency_info_latest_branch_history 

( bank_code, branch_code, ROW, data_type, cu_code, cash_buy, cash_sell, b_exchn_rate, percent_cash_buy, percent_cash_sell, bank_choice_ranking, delivery_bank_code, delivery_commission, delivery_is_use, moddate, moduser, history_date, history_type ) 

VALUES 

( old.bank_code, old.branch_code, old.row, old.data_type, old.cu_code, old.cash_buy, old.cash_sell, old.b_exchn_rate, old.percent_cash_buy, old.percent_cash_sell, old.bank_choice_ranking, old.delivery_bank_code, old.delivery_commission, old.delivery_is_use, old.moddate, old.moduser, NOW(), 'update' );

    END;

$$


DELIMITER ;

[출처] MYSQL]RIGGER 생성|작성자 TK