Hi,
Steffan A. Cline wrote:
on 10/7/07 12:26 PM, Steffan A. Cline at [EMAIL PROTECTED] 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
