From: Martijn Tonies
Date: May 15 2006 7:49am
Subject: Re: Need help with triggers
List-Archive: http://lists.mysql.com/mysql/197986
Message-Id: <013501c677f4$0d6d99d0$cd02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Daevid,

> This is my first trigger I'm trying to write.
>
> I have two tables. 'stores' and 'zipcodes'.
>
> I want to automatically set the latitude and longitude of the store using
> it's zipcode lookup in the zipcode table.
>
> DELIMITER $$;
>
> DROP TRIGGER `store_coord`$$
>
> create trigger `store_coord` BEFORE INSERT on `stores`
> for each row BEGIN
>     UPDATE stores AS s, zipcodes AS z
> SET s.latitude = z.latitude,
>     s.longitude = z.longitude
> WHERE s.zip = z.zip AND s.id = NEW.id;
>   END;
> $$

First things first ...

Updating a row that doesn't exist yet (BEFORE INSERT trigger)
doesn't make sense :-)

Why not simply adjust it like this:
new.latitude := <your looked up latitude>
new.longitude := <your looked up longitude>

Given that it's a "before insert", modifying the NEW.<column> values,
these will be stored in the table.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> DELIMITER ;$$
>
> So when I do this:
>
> insert into `stores` (id, name, zip) values (10,'test company',  14526);
>
> I get this error
>
> Error Code : 1442
> Can't update table 'stores' in stored function/trigger because it is
already
> used by statement which invoked this stored function/trigger.
> (0 ms taken)