我想监控一个表内的几个字段变化。想到使用mysql触发器,于是参考学习写了如下语句,无奈修改一下午,都没运行成功。create trigger `user_monitor` after update on `users_table`
for each row
BEGIN
DECLARE aa int(10);
DECLARE bb int(10);
DECLARE cc int(10);
set @aa:= New.exper - OLD.exper;
set @bb:= New.score - OLD.score;
set @cc:= New.gold - OLD.gold;
if NEW.exper>0 and @aa<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'exp', @aa, NEW.exper, CURRENT_TIMESTAMP);
else if NEW.score>0 and @bb<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'score', @bb, NEW.score, CURRENT_TIMESTAMP);
else if NEW.egold>0 and @cc<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'gold', @cc, NEW.egold, CURRENT_TIMESTAMP);
end if;
END;目前的报错如下:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
for each row
BEGIN
DECLARE aa int(10);
DECLARE bb int(10);
DECLARE cc int(10);
set @aa:= New.exper - OLD.exper;
set @bb:= New.score - OLD.score;
set @cc:= New.gold - OLD.gold;
if NEW.exper>0 and @aa<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'exp', @aa, NEW.exper, CURRENT_TIMESTAMP);
else if NEW.score>0 and @bb<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'score', @bb, NEW.score, CURRENT_TIMESTAMP);
else if NEW.egold>0 and @cc<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'gold', @cc, NEW.egold, CURRENT_TIMESTAMP);
end if;
END;目前的报错如下:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
解决方案 »
- mysql的时间段查询
- 大虾救我.
- 重启 端口是3305的 mysql server如何做
- 刚刚接触MySql,碰到中文1366 的错误,请高手帮忙解答!!
- SQL排序!!!太怪异了!!
- mysql5.0.15版本中文字符问题
- mysql + odbc + vc + IsEOF的问题
- 大家看看我在linux环境下的mysql备份命令有何问题?
- MySQL关于latin1存储汉字的问题
- mysql 的sql语法错误提示能智能一些吗?
- cannot alter type of a column used by a view or rule
- 求一个MYSQL语句!我想统计数据库中某字段等于当前时间的数据总数
set @bb:= New.score - OLD.score;
set @cc:= New.gold - OLD.gold;这不是MYSQL的语法,也不是ORACLE的语法。改成如下
set aa = New.exper - OLD.exper;
set bb = New.score - OLD.score;
set cc = New.gold - OLD.gold;
好像不是这个的问题,之前我也是这样用。不行。
刚刚又根据你的建议修改了。
仍然报第4行语法错误。
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
-- 表的结构 `users_table`
CREATE TABLE IF NOT EXISTS `users_table` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`uname` varchar(10) NOT NULL,
`exper` int(8) NOT NULL,
`score` int(8) NOT NULL,
`gold` int(8) NOT NULL,
PRIMARY KEY (`id`),
KEY `uname` (`uname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;--
-- 转存表中的数据 `users_table`
--INSERT INTO `users_table` (`id`, `uname`, `exper`, `score`, `gold`) VALUES
(1, 'admin', 20, 30, 40);
表wap_usermoniter:CREATE TABLE IF NOT EXISTS `wap_usermoniter` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`uname` varchar(30) NOT NULL,
`type` varchar(5) NOT NULL,
`add` int(7) NOT NULL,
`now_value` int(8) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `uid` (`uid`,`uname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `users_table` (
`uid` int(8) NOT NULL AUTO_INCREMENT,
`uname` varchar(10) NOT NULL,
`exper` int(8) NOT NULL,
`score` int(8) NOT NULL,
`gold` int(8) NOT NULL,
PRIMARY KEY (`uid`),
KEY `uname` (`uname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;--
-- 转存表中的数据 `users_table`
--INSERT INTO `users_table` VALUES (1, 'admin', 20, 30, 40);
mysql> create trigger `user_monitor` after update on `users_table`
-> for each row
-> BEGIN
-> DECLARE aa int(10);
-> DECLARE bb int(10);
-> DECLARE cc int(10);
-> set aa= New.exper - OLD.exper;
-> set bb= New.score - OLD.score;
-> set cc= New.gold - OLD.gold;
-> if NEW.exper>0 and aa<>0 then
-> INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'exp', aa, NEW.exper, CURRENT_TIMESTAMP);
-> elseif NEW.score>0 and bb<>0 then
-> INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'score',bb, NEW.score, CURRENT_TIMESTAMP);
-> elseif NEW.gold>0 and cc<>0 then
-> INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'gold', cc, NEW.gold, CURRENT_TIMESTAMP);
-> end if;
-> END;
-> //
Query OK, 0 rows affected (0.11 sec)mysql>MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
在源表上执行失败。
我重新建了一个数据库,然后执行如下,成功了!但是接下来,就一直失败了,我的操作如下:
1、重新建库建表、建触发器:(成功)。
CREATE DATABASE `ccc` ;use ccc;CREATE TABLE IF NOT EXISTS `wap_usermoniter` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`uname` varchar(30) NOT NULL,
`type` varchar(5) NOT NULL,
`add` int(7) NOT NULL,
`now_value` int(8) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `uid` (`uid`,`uname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;CREATE TABLE IF NOT EXISTS `users_table` (
`uid` int(8) NOT NULL AUTO_INCREMENT,
`uname` varchar(10) NOT NULL,
`exper` int(8) NOT NULL,
`score` int(8) NOT NULL,
`gold` int(8) NOT NULL,
PRIMARY KEY (`uid`),
KEY `uname` (`uname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;--
-- 转存表中的数据 `users_table`
--INSERT INTO `users_table` VALUES (1, 'admin', 20, 30, 40);
delimiter //
create trigger `user_monitor` after update on `users_table`
for each row
BEGIN
DECLARE aa int(10);
DECLARE bb int(10);
DECLARE cc int(10);
set aa= New.exper - OLD.exper;
set bb= New.score - OLD.score;
set cc= New.gold - OLD.gold;
if NEW.exper>0 and aa<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'exp', aa, NEW.exper, CURRENT_TIMESTAMP);
elseif NEW.score>0 and bb<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'score',bb, NEW.score, CURRENT_TIMESTAMP);
elseif NEW.gold>0 and cc<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'gold', cc, NEW.gold, CURRENT_TIMESTAMP);
end if;
END;
//2、我删除触发器:(执行成功)
drop TRIGGER user_monitor;
3、我随便执行一句错误的创建触发器指令:(失败)
create trigger `user_monitor` after update on `users_table`;
4、我执行正确的创建触发器sql:(失败!):
delimiter //
create trigger `user_monitor` after update on `users_table`
for each row
BEGIN
DECLARE aa int(10);
DECLARE bb int(10);
DECLARE cc int(10);
set aa= New.exper - OLD.exper;
set bb= New.score - OLD.score;
set cc= New.gold - OLD.gold;
if NEW.exper>0 and aa<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'exp', aa, NEW.exper, CURRENT_TIMESTAMP);
elseif NEW.score>0 and bb<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'score',bb, NEW.score, CURRENT_TIMESTAMP);
elseif NEW.gold>0 and cc<>0 then
INSERT INTO `wap_usermoniter` VALUES (NULL, OLD.uid, OLD.uname, 'gold', cc, NEW.gold, CURRENT_TIMESTAMP);
end if;
END;
//求解失败的原因。我的另一个老库也因为此原因一直不能创建触发器。明明正确但就是不成功。(ps.最后,在命令模式下,成功了。看来是phpmyadmin的问题,
以上测试是在phpMyAdmin 3.2.0-rc1 + mysql5.1.34-community平台)