Selecting values to a variable inside Mysql triggers

Question

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.

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 $$.

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?

Open to all opinions and suggestions.

Solution 1

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 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.

Solution 2

Did you notice the typo?

OLD_ADDR OLD_AADR

Comments

[Abhijith]

Wow , so fast !! Thank you @MahmoudGamal . I'll check ito out and return soon.

[Abhijith]

Woohooo ... it worked . i am fresh to triggers and procedures in mysql. Thank you very much @MahmoudGamal.