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;
$$
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]
