List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 8 2007 7:38am
Subject:Re: My first trigger in MySQL
View as plain text  
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

Thread
My first trigger in MySQLSteffan A. Cline7 Oct
  • Re: My first trigger in MySQLSteffan A. Cline7 Oct
    • Re: My first trigger in MySQLBaron Schwartz7 Oct
    • Re: My first trigger in MySQLMartijn Tonies8 Oct