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)
