List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:October 7 2007 7:56pm
Subject:Re: My first trigger in MySQL
View as plain text  
on 10/7/07 12:26 PM, Steffan A. Cline at steffan@stripped 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?




Thanks

Steffan

---------------------------------------------------------------
T E L  6 0 2 . 7 9 3 . 0 0 1 4 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline 
Steffan@stripped                             Phoenix, Az
http://www.ExecuChoice.net                                  USA
AIM : SteffanC          ICQ : 57234309
YAHOO : Steffan_Cline   MSN : steffan@stripped
GOOGLE: Steffan.Cline             Lasso Partner Alliance Member
---------------------------------------------------------------



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