List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 10 2005 5:07pm
Subject:Re: Triggers
View as plain text  
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" <steffan@stripped> 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
> Steffan@stripped                             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)" <isales@stripped>
> > Date: Mon, 10 Oct 2005 08:51:55 +0100
> > To: "Steffan A. Cline" <steffan@stripped>
> > Cc: mysql@stripped
> > 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/mysql?unsub=1
> > 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
TriggersSteffan A. Cline9 Oct
  • Re: TriggersRhino9 Oct
    • Re: TriggersGleb Paharenko10 Oct
  • Re: TriggersDBA)10 Oct
    • Re: TriggersSteffan A. Cline10 Oct
      • Re: TriggersSGreen10 Oct
    • Re: TriggersSteffan A. Cline10 Oct