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
