mysql触发器

/*修改老表的sql语句:*/

ALTER TABLE `realwar`.`commanders`     ADD COLUMN `new_commanders_id` INT(11) NULL COMMENT '与新表关联的指挥官' AFTER `wild_type`;

 

/*添加一个触发器,将新表中的数据同步到老表*/

DELIMITER $$

/*在向新表中插入数据的时候,同时把老表commanders表添加一条数据*/

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

 

CREATE

    TRIGGER `insert_commanders` AFTER INSERT ON `t_player_com`

    FOR EACH ROW BEGIN

       IF NEW.play_com_id IS NOT NULL THEN

         INSERT INTO  commanders SET

         ComName = NEW.com_name,/*指挥官名称*/

         ComLevel = NEW.level,/*指挥官等级*/

         UserName = NEW.user_name,/*玩家帐号*/

         CityID = NEW.city_id,/*现在的城市编号*/

         STATUS = NEW.act_status,/*状态*/

         ModelId = NEW.com_id,/*基础指挥官id*/

         new_commander_id = NEW.play_com_id;/*新表中的指挥官id*/

       END IF;

    END;

$$

DELIMITER ;

 

 

/*修改新表t_player_com的sql*/

ALTER TABLE `realwar`.`t_player_com` ADD COLUMN `old_commanders_id` INT(11) NULL COMMENT '与老表关联指挥官id字段' AFTER `lead_count`;

 

 

/*数据删除的同步,在t_player_com中删除一条记录,同时同步到老表(删除一条记录)*/

DELIMITER $$

CREATE

    /*[DEFINER = { user | CURRENT_USER }]*/

    TRIGGER `realwar`.`delete_commanders` AFTER DELETE

    ON `realwar`.`t_player_com`

    FOR EACH ROW BEGIN

       DELETE FROM commanders WHERE new_commanders_id=OLD.play_com_id;/*删除新表中的一条记录,同时更新(删除老表中的数据)*/

    END$$

DELIMITER ;

 

/*修改名字*/

DELIMITER $$

 

CREATE

    /*[DEFINER = { user | CURRENT_USER }]*/

    TRIGGER `realwar`.`update_commanders` AFTER UPDATE

    ON `realwar`.`t_player_com`

    FOR EACH ROW BEGIN

        IF NEW.com_name!=OLD.com_name THEN

         UPDATE commanders SET commanders.ComName=NEW.com_name WHERE commanders.new_commanders_id=OLD.play_com_id;/*更新指挥官的名字,同时更新老表中的数据*/

       END IF; 

    END$$

DELIMITER ;