List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 16 2006 4:00am
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;
 >> > > $$

Lose the UPDATE clause. All you need (and all that's permitted) is SET 
NEW ...
And lose the WHERE clause. It's redundant (also not permitted).
If you need a value from another table, DECLARE a var and write a SELECT 
INTO for it.
(BTW, if your database looks up latitude & longitude from zipcodes, why 
bother copying them into store_coord?)

PB


>>> 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.
>>     
>
>   
>>> 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.
>>>       
>
> I must not be doing something right, I've tried all these combinations and
> ALL give that same error message:
>
> 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 NEW.latitude = z.latitude,
> 	    NEW.longitude = z.longitude
> 	WHERE s.zip = z.zip AND s.id = NEW.id;
>   END;
> $$
>
> 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 NEW.latitude := z.latitude,
> 	    NEW.longitude := z.longitude
> 	WHERE s.zip = z.zip AND s.id = NEW.id;
>   END;
> $$
>
> DELIMITER $$;
> DROP TRIGGER `store_coord`$$
> create trigger `store_coord` AFTER 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;
> $$
>
> What am I doing wrong?
>
>
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006
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