This does not work as I am drawing the information from 2 tables. I did use
the NEW operator and every combination I could think of. Below I'll include
the triggers and so on. What the deal is that I have a query to find
locations within a certain radius. BUT from what I read in the table with
locations I need to have latitude and longitude. I was trying to make this
automatically populated so that the user would not have to do it.
Anyhow :
Triggers:
CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW UPDATE
locations, zipcodes SET NEW.locations.test1=zipcodes.lat,
NEW.locations.test2=zipcodes.lon WHERE (NEW.locations.zip=zipcodes.zip AND
(NEW.locations.test1 IS NULL) AND (NEW.locations.test2 is NULL));
CREATE TRIGGER UpdPopCoord AFTER UPDATE ON locations FOR EACH ROW UPDATE
locations, zipcodes SET NEW.locations.test1=zipcodes.lat,
NEW.locations.test2=zipcodes.lon WHERE (NEW.locations.zip=zipcodes.zip AND
(NEW.locations.test1 IS NULL) AND (NEW.locations.test2 is NULL));
The query necessitating the lat and lon:
-sql=('SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city,
b.state, b.zip,
ROUND((3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2))))),2) AS distance
FROM zipcodes a, locations b
WHERE
a.zip = "' ($zip) '"
GROUP BY distance
having distance <= ' ($range) ';');
The table structures :
Thanks
Steffan
---------------------------------------------------------------
T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
---------------------------------------------------------------
> From: "Ian Sales (DBA)" <[EMAIL PROTECTED]>
> Date: Mon, 10 Oct 2005 08:51:55 +0100
> To: "Steffan A. Cline" <[EMAIL PROTECTED]>
> Cc: mysql@lists.mysql.com
> Subject: Re: Triggers
>
> Steffan A. Cline wrote:
>
>> Upon insert or update I get the following error:
>>
>> ERROR 1442 (HY000): Can't update table 'locations' in stored
>> function/trigger because it is already used by statement which invoked this
>> stored function/trigger.
>>
>> What exactly is the meaning of this? Is there no way around this? I only
>> want to update the one that was just inserted/updated.
>>
>>
> - you cannot use a table in a trigger which is triggered by an action on
> that self-same table, as this is recursive. You can, however, use NEW as
> a synonym for the data being changed which fires off the trigger. E.g.,
>
> CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW
> UPDATE zipcodes
> SET zipcodes.lat=NEW.lat, zipcodes.lon=NEW.lon
> WHERE zipcodes.zip=NEW.zip;
>
>
> - ian
>
> --
> +-------------------------------------------------------------------+
> | Ian Sales Database Administrator |
> | |
> | "All your database are belong to us" |
> | ebuyer http://www.ebuyer.com |
> +-------------------------------------------------------------------+
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
