List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:October 9 2005 7:40am
Subject:Triggers
View as plain text  
I am trying to use triggers for the first time with MySQL 5.0. I have read
the manual but I am not understanding why I would run into the following
problem.

I created the following triggers:

CREATE TRIGGER UpdPopCoord AFTER UPDATE ON locations FOR EACH ROW
UPDATE locations, zipcodes
SET locations.lat=zipcodes.lat, locations.lon=zipcodes.lon
WHERE  (locations.zip=zipcodes.zip AND (locations.lat IS NULL) AND
(locations.lon is NULL));

CREATE TRIGGER InsPopCoord AFTER INSERT ON locations FOR EACH ROW
UPDATE locations, zipcodes
SET locations.lat=zipcodes.lat, locations.lon=zipcodes.lon
WHERE  (locations.zip=zipcodes.zip AND (locations.lat IS NULL) AND
(locations.lon is NULL));

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.



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
---------------------------------------------------------------


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