 | | | My first trigger in MySQL | My first trigger in MySQL 2007-10-07 - By Steffan A. Cline
Back
on 10/7/07 12:26 PM, Steffan A. Cline at steffan@(protected) 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@(protected) Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC ICQ : 57234309
YAHOO : Steffan_Cline MSN : steffan@(protected)
GOOGLE: Steffan.Cline Lasso Partner Alliance Member
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|