You could re-write your query so that you get the lat and lon for each
location as you run the query. Since each location has a zip code, this
just means only one extra join per query. That also means you won't need
the triggers as you are looking up each locations lat and lon on the fly.
The down side to this is the extra join will slow down your Cartesian
product just a smidge.
SELECT a.zip, l.state,l.storename, l.address1, l.address2, l.city,
l.state, l.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
INNER JOIN locations l
INNER JOIN zipcodes b
ON b.zip = l.zip
WHERE a.zip = "' ($zip) '"
GROUP BY distance
HAVING distance <= ' ($range) ';');
OR the triggers may still work if you just modify their definitions (not
tested):
CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW UPDATE
NEW INNER JOIN zipcodes ON NEW.zip = zipcodes.zip SET
NEW.test1=zipcodes.lat,
NEW.test2=zipcodes.lon WHERE (NEW.locations.test1 IS NULL) AND
(NEW.locations.test2 is NULL));
CREATE TRIGGER UpdPopCoord AFTER UPDATE ON locations FOR EACH ROW UPDATE
NEW INNER JOIN zipcodes ON NEW.zip = zipcodes.zip SET
NEW.test1=zipcodes.lat,
NEW.test2=zipcodes.lon WHERE (NEW.locations.test1 IS NULL) AND
(NEW.locations.test2 is NULL));
Inside a trigger the keyword NEW already stands in for the row/table
being updated. Trying to say "NEW.location.columnname" is redundant and
results in bad syntax. You need to use "NEW.columnname" instead.
I also personally hate the "comma-list" format for creating an inner join
(or Cartesian product as we do in the first query) so I replaced all of
your commas with explicit INNER JOIN tokens. That way if you want to use
NULLs when you do not have a zipcode for your `lat` and `lon` values, all
you need to do is change the word INNER to the word LEFT. As written, I
don't believe the row will finish inserting if there is no location record
for the zip code you are trying to INSERT or UPDATE because the INNER JOIN
will fail to match a row so the UPDATE will not change anything and if the
UPDATE does nothing.... I just don't know where that leaves the execution
of the statement that started the trigger.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Steffan A. Cline" <[EMAIL PROTECTED]> wrote on 10/10/2005 12:25:00 PM:
> 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]
>
