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) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
