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  
Hi,

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`
> FOR EACH ROW
>     UPDATE users SET users.username = concat(users.firstname,"
> ",users.lastname) where users.id=NEW.id;
> END
> ;
> 
> 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:

http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html

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:

http://bugs.mysql.com/bug.php?id=19686

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

Baron
Thread
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