> > 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.
> > 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. I must not be doing something right, I've tried all these combinations and ALL give that same error message: 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 NEW.latitude = z.latitude, NEW.longitude = z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ 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 NEW.latitude := z.latitude, NEW.longitude := z.longitude WHERE s.zip = z.zip AND s.id = NEW.id; END; $$ DELIMITER $$; DROP TRIGGER `store_coord`$$ create trigger `store_coord` AFTER 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; $$ What am I doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]