List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 15 2006 7:49am
Subject:Re: Need help with triggers
View as plain text  
Daevid,

> 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.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> 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