Mysql触发器例子（备忘）

/**
 * update触发器
 */
DELIMITER $$

DROP TRIGGER `updateEat`$$

CREATE
    TRIGGER `updateEat` AFTER UPDATE ON `TB_BACK_EAT` 
    FOR EACH ROW BEGIN
        DECLARE _gameId INT;
        DECLARE _drawDateId INT;
        DECLARE _number VARCHAR(4);
        DECLARE _status INT;
        DECLARE _big DECIMAL(13,6);
        DECLARE _sml DECIMAL(13,6);
        DECLARE _num_game_draw VARCHAR(100);
        
        SET _gameId = NEW.game_id;
        SET _drawDateId = NEW.drawDate_id;
        SET _number = NEW.number;
        SET _status = NEW.status;
        SET _big = NEW.big;
        SET _sml = NEW.small;
         SET _num_game_draw = CONCAT(_number,'_',_gameId,'_',_drawDateId);
	IF _status=1 THEN  -- 表示取消
	     INSERT INTO TB_BACK_EAT_REPORT(gameId, drawDateId,WEEKDAY,number,fromBig,fromSml,big,small,putBig,putSml,outBig,outSml,num_game_draw)
	     VALUES(_gameId,_drawDateId,1,_number,_big,_sml,0,0,0,0,0,0,_num_game_draw)
	     ON DUPLICATE KEY 
	     UPDATE fromBig= CASE WHEN (fromBig-_big)>=0  THEN fromBig-_big ELSE 0 END,
	            fromSml= CASE WHEN (fromSml-_sml)>=0  THEN fromSml-_sml ELSE 0 END;
	END IF;
    END;
$$

DELIMITER ;

/**
 * insert的触发器
 */
DELIMITER $$

DROP TRIGGER `insertEat`$$

CREATE  TRIGGER `insertEat` AFTER INSERT ON `TB_BACK_EAT` 
    FOR EACH ROW BEGIN
        DECLARE _gameId INT;
        DECLARE _drawDateId INT;
        DECLARE _number VARCHAR(4);
        DECLARE _status INT;
        DECLARE _big DECIMAL(13,6);
        DECLARE _sml DECIMAL(13,6);
        DECLARE _num_game_draw VARCHAR(100);
        
        SET _gameId = NEW.game_id;
        SET _drawDateId = NEW.drawDate_id;
        SET _number = NEW.number;
        SET _status = NEW.status;
        SET _big = NEW.big;
        SET _sml = NEW.small;
        
        SET _num_game_draw = CONCAT(_number,'_',_gameId,'_',_drawDateId);

	IF _status=0 THEN  -- 表示添加
	     INSERT INTO TB_BACK_EAT_REPORT(gameId, drawDateId,WEEKDAY,number,fromBig,fromSml,big,small,putBig,putSml,outBig,outSml,num_game_draw)
	     VALUES(_gameId,_drawDateId,1,_number,_big,_sml,0,0,0,0,0,0,_num_game_draw)
	     ON DUPLICATE KEY 
	     UPDATE fromBig=fromBig+_big,fromSml=fromSml+_sml;
	END IF;
    END;
$$

DELIMITER ;