I've never written a trigger, but the error implies that you can't update the stores table when in a trigger ON the stores table.
This makes sense, especially if you consider an 'UPDATE' trigger - the trigger would become recursive. So, rather than deciding which combination are recursive and which aren't, maybe there's a general rule that you can't change the table on which the trigger is placed? Hopefully someone will let you (and me) know if I don't know what I'm talking about! Quentin -----Original Message----- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, 16 May 2006 2:41 p.m. To: 'Martijn Tonies'; mysql@lists.mysql.com Subject: RE: Need help with triggers > > 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] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
