I have never written a trigger in MySQL but I've done a number of them in DB2 so, assuming the same principles apply to both:
I think the problem is that you are attempting to make the triggered action - the UPDATE or INSERT - occur in the same table (or a join involving the same table) as the one which contained the triggering action. For example, after an update of the 'locations' table, you want MySQL to update 'locations' (or join involving 'locations'): this looks circular/recursive to me and I suspect from the error message that is not permitted by MySQL. I expect that your problems will go away if the table that triggers the action is always different from the table that is changed as a result of the trigger. In other words, if an action in Table X is your trigger, the action taken when the trigger is pulled should take place in some table OTHER THAN Table X. I just skimmed the article on CREATE TRIGGER in the manual and don't see any prohibition against making the triggered action take place in the same table as the one that caused the triggering action; I suspect that was an oversight on the part of the technical writer who may have (reasonably) assumed that no one would want the triggered action to occur in the same table as the triggering action. Then again, maybe its not there because it's okay for both the triggered and triggering actions to be on the same table. Maybe Paul Dubois or one of the other writers can address this point with certainty. Rhino ----- Original Message ----- From: "Steffan A. Cline" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Sunday, October 09, 2005 3:40 AM Subject: Triggers > 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 > [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 > --------------------------------------------------------------- > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.344 / Virus Database: 267.11.13/124 - Release Date: 07/10/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/126 - Release Date: 09/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
