CREATE
TRIGGER `beforeinsertevevtdata`
AFTER INSERT
ON `eventdata`
FOR EACH ROW BEGIN
SELECT COUNT(*) INTO @rownum FROM `eventdata_new` WHERE accountID = new.accountID;
IF(@rownum > 0) THEN
UPDATE `eventdata_new` SET timestamp = new.timestamp, latitude = new.latitude, longitude = new.longitude, altitude = new.altitude, TIME = new.time WHERE accountID = new.accountID;
ELSE
INSERT INTO `eventdata_new` (accoutID, TIMESTAMP, latitude, longitude, altitude, TIME) VALUES (new.accountID, new.timestamp, new.latitude, new.longitude, new.altitude, new.time);
END IF;
END;我想在表eventdata插入数据时,在eventdata_new查询一下,当eventdata_new有accountIDeventdata插入的这条数据的accountID时UPDATE,没有时INSERT,但我这么写一只报错
TRIGGER `beforeinsertevevtdata`
AFTER INSERT
ON `eventdata`
FOR EACH ROW BEGIN
SELECT COUNT(*) INTO @rownum FROM `eventdata_new` WHERE accountID = new.accountID;
IF(@rownum > 0) THEN
UPDATE `eventdata_new` SET timestamp = new.timestamp, latitude = new.latitude, longitude = new.longitude, altitude = new.altitude, TIME = new.time WHERE accountID = new.accountID;
ELSE
INSERT INTO `eventdata_new` (accoutID, TIMESTAMP, latitude, longitude, altitude, TIME) VALUES (new.accountID, new.timestamp, new.latitude, new.longitude, new.altitude, new.time);
END IF;
END;我想在表eventdata插入数据时,在eventdata_new查询一下,当eventdata_new有accountIDeventdata插入的这条数据的accountID时UPDATE,没有时INSERT,但我这么写一只报错
解决方案 »
- 求助 win7 asp 连接系统DSN ( mysql odbc 5.2w driver),总出现500错误
- 用Mysql查询出A表中与掉B表相同的部分,并输出结果
- 插入数据时,重复数据只插入一次
- 多字段比较大小
- postgres存储过程问题,谢啦...
- 按照a.evid 的各个分组下 只能要全部b.status=1的分组
- 按照已有的主键来分表是否有意义呢?
- mysql建立外键方式,给个理由就可以!
- mysql中文查询
- Mysql4.x版在Windows平台下的数据库同步实现
- XP系统下 Mysql如何使用mysqldump和mysqlhotcopy 备份 恢复数据库
- 得到一条记录 按照 成绩字段排序 的名次
如果accountID是主键的话,参考insert into ON DUPLICATE KEY UPDATE
CREATE
TRIGGER `beforeinsertevevtdata`
AFTER INSERT
ON `eventdata`
FOR EACH ROW BEGIN
SELECT COUNT(*) INTO @rownum FROM `eventdata_new` WHERE accountID = new.accountID;
IF(@rownum > 0) THEN
UPDATE `eventdata_new` SET `timestamp` = new.`timestamp`, latitude = new.latitude, longitude = new.longitude, altitude = new.altitude, `TIME` = new.time WHERE accountID = new.accountID;
ELSE
INSERT INTO `eventdata_new` (accoutID, `TIMESTAMP`, latitude, longitude, altitude, TIME) VALUES (new.accountID, new.timestamp, new.latitude, new.longitude, new.altitude, new.time);
END IF; END;
$$
建议直接在MYSQL行命令工具中试一下。另外建议直接用 insert into ... on duplicate 语句这样反而简单一些。 还有timestamp 等是关键字,尽量不要用来做字段名。CREATE TRIGGER `beforeinsertevevtdata`
AFTER INSERT ON `eventdata`
FOR EACH ROW
INSERT INTO `eventdata_new` (accoutID, `TIMESTAMP`, latitude, longitude, altitude, `TIME`)
VALUES (new.accountID, new.timestamp, new.latitude, new.longitude, new.altitude, new.time)
ON DUPLICATE KEY UPDATE `timestamp`=VALUES(timestamp),
latitude=VALUES(latitude),
longitude=VALUES(longitude),
altitude=VALUES(altitude),
`TIM`E=VALUES(TIME);
不是版本的问题,是你的这个表上已经有了一个 AFTER INSERT 的触发器了。
select * from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_NAME='beforeinsertevevtdata'检查一下有哪些触发器了。
就是报#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
没有正确过,但的确有触发器被建立了............汗
这个是已有触发器了。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html