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  

> 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.
> 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 = AND =;
>   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
My thoughts:
Database development questions? Check the forum!

> 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
> used by statement which invoked this stored function/trigger.
> (0 ms taken)

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