mysql trigger触发器 一张表一个字段改变 更新另一张表对应字段

触发器语句先贴出来：

DELIMITER $$
CREATE TRIGGER goods_sku_shopprice_trigger AFTER UPDATE ON t_sku FOR EACH ROW
BEGIN
SET @oldPrice = (SELECT t_goods.shop_price FROM t_goods WHERE t_goods.goods_id=new.goods_id LIMIT 1);
IF new.shop_price<@oldPrice THEN 
UPDATE t_goods SET t_goods.shop_price=new.shop_price WHERE t_goods.goods_id=new.goods_id;

END IF; 

END $$
DELIMITER ;

 

注意点1：select t_goods.shop_price from t_goods where t_goods.goods_id=new.goods_id limit 1;不能直接用作表达式比较，因为语句很多可能返回多个值。应该将结果赋值给一个变量@oldPrice然后比较。

错误示范：new.shop_price<(SELECT t_goods.shop_price FROM t_goods WHERE new.goods_id=t_goods.goods_id LIMIT 0,1)
正确示范：new.shop_price<@oldPrice

注意点2：给变量赋值时如果赋值结果是由sql语句查询而来记得sql语句加括号，否则也会出错。

错误示范：set @oldPrice = select t_goods.shop_price from t_goods where t_goods.goods_id=new.goods_id limit 1;

正确示范：set @oldPrice = (select t_goods.shop_price from t_goods where t_goods.goods_id=new.goods_id limit 1);

注意点3：begin 和 end之前是语句，就像其它编程语言一样。因此每一句写完记得加分号结尾。

注意点4：DELIMITER 后面注意只有一个空格。（这个坑我踩过，虽然只花了不到20分钟，但是很难发现）

   下面这个触发器很简单。当count=0时做插入操作。大于0时做修改操作。但是注意第一行的DELIMITER $$,中间是只能有一个空格的，多了一个空格的话，会报错，并且是至少不止一个错误，应该是将空格当成结束符了。

DELIMITER $$
CREATE TRIGGER d_mmiddle_add_trigger AFTER INSERT ON t_middle FOR EACH ROW 
BEGIN
	SET @count = (SELECT COUNT(*) FROM t_mmiddle WHERE t_mmiddle.`m_pid`=new.m_pid AND t_mmiddle.`m_uid`=new.m_uid);
	IF  @count = 0 THEN
		INSERT INTO t_mmiddle VALUES (new.m_uid,new.m_pid,new.m_id,new.chip_number,new.gz_type,
			new.zw_type,new.bz_type,new.state,new.pack_info,new.is_black,
			new.out_time,new.create_time,new.forbid_id,new.forbid_state,new.is_teach,
			new.builder,new.accountId,'add');
	ELSE
		UPDATE t_mmiddle SET m_id=new.m_id,m_pid=new.m_pid, m_uid=new.m_uid,chip_number=new.chip_number,gz_type=new.gz_type,
			zw_type=new.zw_type,bz_type=new.bz_type,state=new.state,pack_info=new.pack_info,is_black=new.is_black,
			out_time=new.out_time,create_time=NOW(),forbid_id=new.forbid_id,forbid_state=new.forbid_state,is_teach=new.is_teach,
			builder=new.builder,accountId=new.accountId,oper='upt' WHERE m_pid=new.m_pid AND m_uid=new.m_uid;
	END IF;
END $$
DELIMITER ;