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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
