Hi, > 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?
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 without > 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. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
