List:General Discussion« Previous MessageNext Message »
From:Rhino Date:October 9 2005 1:45pm
Subject:Re: Triggers
View as plain text  
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" <steffan@stripped>
To: <mysql@stripped>
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
> 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
> ---------------------------------------------------------------
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
> -- 
> 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

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