List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 15 2006 6:07am
Subject:Need help with triggers
View as plain text  
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)

Thread
Need help with triggersDaevid Vincent15 May
  • Re: Need help with triggersMartijn Tonies15 May
    • RE: Need help with triggersDaevid Vincent16 May
RE: Need help with triggersQuentin Bennett16 May
Re: Need help with triggersPeter Brawley16 May