List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:October 10 2005 4:25pm
Subject:Re: Triggers
View as plain text  
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 :

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `locations` (
  `storename` varchar(255) default NULL,
  `address1` varchar(255) default NULL,
  `address2` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `state` varchar(255) default NULL,
  `zip` varchar(255) default NULL,
  `phone` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `fax` varchar(255) default NULL,
  `comments` text,
  `firstname` varchar(255) default NULL,
  `lastname` varchar(255) default NULL,
  `lat` float default NULL,
  `lon` float default NULL,
  `id` bigint(11) NOT NULL auto_increment,
  `test1` float default NULL,
  `test2` float default NULL,
  PRIMARY KEY  (`id`),
  KEY `zip` (`zip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `zipcodes` (
  `zip` varchar(5) default NULL,
  `city` varchar(100) default NULL,
  `county` varchar(100) default NULL,
  `state` varchar(100) default NULL,
  `areacode` varchar(10) default NULL,
  `fips` varchar(10) default NULL,
  `timezone` varchar(10) default NULL,
  `dst` varchar(10) default NULL,
  `lat` float default NULL,
  `lon` float default NULL,
  KEY `zip` (`zip`),
  KEY `lat` (`lat`),
  KEY `lon` (`lon`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


SET FOREIGN_KEY_CHECKS = 1;

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
> 

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