关于一个头痛的问题,触发器?
这几天部署一个系统,用的tomcat6.0,数据库mysql5.0,起初在windows下面测试运行一点问题都没有。然后部署在Linux redhat5(数据库也是mysql5.0,tomcat6.0)下面出现了一个问题:在新增用户的时候会出现以下异常:失败SQLException: Can't update table 'user_info' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 查过了,大部分说是由于有insert的问题,要用set,下面是我触发器代码,及存储过程代码:这个是触发器的代码,直接复制的navicat的
begin
CALL user_yongpin_synchro();
end
BEGIN
#Routine body goes here...
DELETE FROM user_yongpin WHERE user_yongpin.mtime < now()
OR NOT EXISTS(
SELECT 1 FROM job_ploy
WHERE job_ploy.yongpin_detail_ID = user_yongpin.yongpinID
AND job_ploy.job_type_ID =
(SELECT user_info.job_type_ID
FROM user_info
WHERE user_info.ID = user_yongpin.user_info_ID)
);
INSERT INTO user_yongpin
(yongpinID, remain, mtime, user_info_ID)
SELECT
job_ploy.yongpin_detail_ID, job_ploy.yongpin_num,
DATE_ADD(user_info.etime,INTERVAL (((YEAR(NOW()) - YEAR(user_info.etime)) * 12) + (MONTH(NOW()) - MONTH(user_info.etime))) + job_ploy.yongpin_limit - (((YEAR(NOW()) - YEAR(user_info.etime)) * 12) + (MONTH(NOW()) - MONTH(user_info.etime))) % job_ploy.yongpin_limit MONTH),
user_info.ID
FROM job_ploy
JOIN user_info ON job_ploy.job_type_ID = user_info.job_type_ID
WHERE NOT EXISTS (
SELECT user_yongpin.ID FROM user_yongpin
WHERE user_yongpin.user_info_ID = user_info.ID
AND user_yongpin.yongpinID = job_ploy.yongpin_detail_ID
AND user_yongpin.mtime > now()
/*AND user_info.job_type_ID = job_ploy.job_type_ID*/
);
END