List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:October 9 2005 9:22pm
Subject:Re: Triggers
View as plain text  
Hello.

> 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

I hope this is a temporary limitation. From:
  http://dev.mysql.com/doc/mysql/en/news-5-0-12.html

"Recursive triggers are detected and disallowed. (Bug #11896, Bug #12644)"

From the source (sql/sql_base.cc):

 /*
   If we are in stored function or trigger we should ensure that
   we won't change table that is already used by calling statement.
   So if we are opening table for writing, we should check that it
   is not already open by some calling stamement.
 */


Rhino wrote:
> 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 ---

-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



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