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?
> 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;
> 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 :)