List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:October 7 2007 9:05pm
Subject:Re: My first trigger in MySQL
View as plain text  

Steffan A. Cline wrote:
> on 10/7/07 12:26 PM, Steffan A. Cline at steffan@stripped wrote:
>> I am new to triggers and am trying to figure this out. The goal I have is to
>> set it up so that after a row in the users table is updated it will check to
>> see if the affected row has a username in the username field and if not to
>> update that row with a concatenation of firstname + " " + lastname.
>> Does anyone have a good example of how to do this?
>> Thanks
>> Steffan
> I tried this : 
> CREATE TRIGGER username_check AFTER UPDATE ON `users`
>     UPDATE users SET users.username = concat(users.firstname,"
> ",users.lastname) where;
> ;
> But it throws the error:
> "Can't update table 'users' in stored function/trigger because it is already
> used by statement which invoked this stored function/trigger."
> Is this to stop infinite recursion? Is there anyway around this?
> In thinking on this if I do a BEFORE rather than AFTER is there anyway to
> test the query to see if it is updating the table and if there is no
> username being submitted to append it to the query so it will be set without
> causing such a recursion?

You're running into one of their many limitations.  See:

I'm not an expert on them, but as a side note:  I personally don't use 
triggers in MySQL.  I consider them rather poorly implemented in MySQL 5 
and I'm afraid of them breaking replication, or doing something else I 
don't like.  When we took a look at them at my employer, we found basic 
things wrong with them, like this:

Therefore I do not trust them at all, and that's why I don't know a 
whole lot about how to use them :)

My first trigger in MySQLSteffan A. Cline7 Oct
  • Re: My first trigger in MySQLSteffan A. Cline7 Oct
    • Re: My first trigger in MySQLBaron Schwartz7 Oct
    • Re: My first trigger in MySQLMartijn Tonies8 Oct