MySQL: Trigger - Change value for different columns

Question

I've got a table like this

 id     firstname   lastname    fullname
 1      alex        car         alex car
 2      tony        table       tony table

If the firstname or lastname gets changed I want that the database automatically changes the fullname too.

I tried it with this trigger

CREATE TRIGGER mytrigger
AFTER UPDATE
   ON mytable FOR EACH ROW

BEGIN

    UPDATE `mydb`.`mytable` SET `fullname` = CONCAT(NEW.firstname, " ",NEW.lastname)    

END;

But I get this error:

#1442 - Can't update table 'mytable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Thanks you.

Gordon Linoff · Accepted Answer · 2015-12-09 13:04:28Z

1

Use a before update trigger:

CREATE TRIGGER mytrigger BEFORE UPDATE
   ON mytable FOR EACH ROW
BEGIN
    set new.fullname = CONCAT(NEW.firstname, ' ' , NEW.lastname) ;
END;

As a note: your use of update would give you surprising results. The update has not where clause so it would set the fullname column in all rows in the table.

answered Dec 9, 2015 at 13:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coder Over a year ago

I tried it with a BEFORE trigger but it did not work.

Collectives™ on Stack Overflow

MySQL: Trigger - Change value for different columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related