mysql 触发器 return_mysql触发器的有关问题 Not allowed to return a result set from a trigger...

mysql触发器的问题 Not allowed to return a result set from a trigger

研究了一晚上，转化一个mssql到mysql的触发器，到最后这个问题不知出在何处，请指教。。。

delimiter //

CREATE TRIGGER AccountPayOnlineLog

AFTER INSERT

ON cb_intl_2009.cb_payonlinelog

FOR EACH ROW

BEGIN

DECLARE TMP_oldBalance INT;

DECLARE TMP_newBalance INT;

DECLARE TMP_type VARCHAR(50);

DECLARE TMP_remark VARCHAR(50);

DECLARE TMP_balance_temp INT;

IF NEW.result=N'充值成功' THEN

BEGIN

-- 100元送5元，200元送15元，500元送50元，800送100，1000送150，2000送350。积分双倍。

IF (locate(N'pay19',new.pay_mode)<=0 AND new.amount>=10000) THEN

BEGIN

IF new.Amount=10000 THEN

SET TMP_balance_temp=500;

ELSEIF new.Amount=20000 THEN

SET TMP_balance_temp=1500;

ELSEIF new.Amount=50000 THEN

SET TMP_balance_temp=5000;

ELSEIF new.Amount=80000 THEN

SET TMP_balance_temp=10000;

ELSEIF new.Amount=100000 THEN

SET TMP_balance_temp=15000;

ELSEIF new.Amount=200000 THEN

SET TMP_balance_temp=35000;

ELSE

SET TMP_balance_temp=0;

END IF;

end;

ELSE

SET TMP_balance_temp=0;

END IF;

SELECT TMP_newBalance=balance from cb_USER where telephone=new.telephone;

SET TMP_Type=N'用户充值';

SET TMP_oldBalance=TMP_newBalance-TMP_Amount-TMP_balance_temp ;

SET TMP_Remark=CONCAT(N'【cb_payonlinelog】表ID：',new.id);

INSERT INTO cb_AccountLog(Telphone,OldBalance,NewBalance,Amount,Type,PayMode,Remark,Balance_Temp)

VALUES(new.telephone,TMP_oldBalance,TMP_newBalance,new.amount,TMP_Type,new.pay_mode,TMP_Remark,TMP_balance_temp);

END;

END IF;

end;

//

------解决方案--------------------

问题在“SELECT TMP_newBalance=balance from cb_USER where telephone=new.telephone;”

trigger 中不允许返回select出来的结果集

------解决方案--------------------

MYSQL中语法应该如下 。

SELECT balance into TMP_newBalance from cb_USER where telephone=new.telephone;

------解决方案--------------------

SELECT TMP_newBalance=balance from cb_USER where telephone=new.telephone;->

SELECT balance INTO TMP_newBalance from cb_USER where telephone=new.telephone;

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~