Ok I've seen many similar questions but crawling over the answers couldn't make my trigger error free!
Result I need is: Whenever a new value is inserted in the database table temp_pool
, it triggers and if the new address is not equal to the previous address value with the same dev_id
as that of this NEW.dev_id
insert the new values to location table.
好的,我已经看过很多类似的问题,但爬过答案无法使我的触发器无错误!我需要的结果是:每当在数据库表temp_pool中插入新值时,它就会触发,如果新地址不等于先前的地址值,并且与此NEW.dev_id具有相同的dev_id,则将新值插入位置表。
Here is the query (sample):
这是查询(示例):
CREATE TRIGGER filter
after insert on geo.temp_pool
for each row
BEGIN
DECLARE OLD_ADDR VARCHAR(2048);
OLD_AADR = select address from temp_pool where dev_id like NEW.dev_id
order by date desc, time desc limit 1;
IF (OLD_ADDR != NEW.address) THEN
INSERT INTO a3380361_geo.location
VALUES (NEW.dev_id,NEW.address,NEW.lat,NEW.lng,NEW.date,NEW.time);
END IF;
END
$$
I am using the phpMyAdmin editor and set the delimiter to $$
.
我正在使用phpMyAdmin编辑器并将分隔符设置为$$。
The error that I am getting is:
我得到的错误是:
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 '= select address from temp_pool where 1 order by date desc, time desc limit 1; ' at line 5
I strongly believe that there is some problem with assigning values from SELECT
to a variable [OLD_ADDR
], so is there any way to solve my issue?
The logic is simple and the requirement is understandable from the query, right?
我坚信将SELECT中的值分配给变量[OLD_ADDR]存在一些问题,那么有什么方法可以解决我的问题吗?逻辑很简单,查询中的要求是可以理解的,对吧?
Open to all opinions and suggestions.
对所有意见和建议持开放态度。
2 个解决方案
#1
4
Instead of:
代替:
OLD_AADR = select address from temp_pool where dev_id like NEW.dev_id
Use:
使用:
SET OLD_AADR = (select address
from temp_pool
where dev_id like NEW.dev_id
order by address
limit 1);
Or using the non-standard SELECT
assignment statement(not sure whether mysql supports it or not):
或者使用非标准的SELECT赋值语句(不确定mysql是否支持):
SELECT OLD_AADR = address from temp_pool where dev_id like NEW.dev_id
order by address
limit 1;
Not that in both cases the SELECT
statement has to return only a scalar value. Thats why I used LIMIT 1
.
并非在两种情况下,SELECT语句都必须仅返回标量值。这就是我使用LIMIT 1的原因。
#2
3
Did you notice the typo?
你注意到了错字吗?
OLD_ADDR OLD_AADR
OLD_ADDR OLD_AADR
#1
4
Instead of:
代替:
OLD_AADR = select address from temp_pool where dev_id like NEW.dev_id
Use:
使用:
SET OLD_AADR = (select address
from temp_pool
where dev_id like NEW.dev_id
order by address
limit 1);
Or using the non-standard SELECT
assignment statement(not sure whether mysql supports it or not):
或者使用非标准的SELECT赋值语句(不确定mysql是否支持):
SELECT OLD_AADR = address from temp_pool where dev_id like NEW.dev_id
order by address
limit 1;
Not that in both cases the SELECT
statement has to return only a scalar value. Thats why I used LIMIT 1
.
并非在两种情况下,SELECT语句都必须仅返回标量值。这就是我使用LIMIT 1的原因。
#2
3
Did you notice the typo?
你注意到了错字吗?
OLD_ADDR OLD_AADR
OLD_ADDR OLD_AADR