 | | | My first trigger in MySQL | My first trigger in MySQL 2007-10-08 - By Martijn Tonies
Back
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/mysql?unsub=mysql@(protected)
|
|
|