> 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
> used by statement which invoked this stored function/trigger."
> Is this to stop infinite recursion? Is there anyway around this?
Simply using a BEFORE UPDATE and BEFORE INSERT trigger and then:
new.username = concat(new.firstname, ' ', new.lastname)
should do the trick.
> 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
> causing such a recursion?
If you're doing an UPDATE without updating the firstname/lastname column,
the NEW values will contain the current values.
If you're submitting a username yourself, simply test for NULL or '' first
to see if you want to overwrite it.
Database Workbench - development tool for MySQL, and more!
Database development questions? Check the forum!