From: Daevid Vincent
Date: May 15 2006 6:07am
Subject: Need help with triggers
List-Archive: http://lists.mysql.com/mysql/197984
Message-Id: <008301c677e5$da6758e0$450a0a0a@locutus>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

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)