create trigger merges firstname and lastname fields into one on field update or insert-CodePudding

I am trying to learn more about MySQL triggers

I need a trigger that merges firstname and lastname columns into one fullname column when that firstname or lastname field is updated or a new row is created/inserted

Something like?

CREATE TRIGGER `fullname` BEFORE INSERT ON users 
FOR EACH ROW Set users.fullname = CONCAT(users.firstname, ' ', users.lastname);

CodePudding user response：

'Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger' - https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

in other words

CREATE TRIGGER `fullname` BEFORE INSERT ON users 
FOR EACH ROW Set NEW.fullname = CONCAT(NEW.firstname, ' ', NEW.lastname);

and in an update trigger

CREATE TRIGGER `fullname` BEFORE update ON users 
    FOR EACH ROW Set NEW.fullname = CONCAT(NEW.firstname, ' ', NEW.lastname);

NB mysql does not have an insert and update trigger you need one of each