how to create trigger to do concatenation of fields
database, mysql, sql, sql-update
Solution
You cannot change a table while the INSERT trigger is firing. You can, however, create a trigger before inserting the record.
DELIMITER |
CREATE TRIGGER `fullname` BEFORE INSERT ON `user`
FOR EACH ROW
BEGIN
SET NEW.full_name = CONCAT(NEW.first_name, ' ', NEW.last_name);
END |
DELIMITER ;
Problem
I have 1 table like this: ``` user ---------------------------------------- id | first_name | last_name | full_name ---------------------------------------- ``` I want to write a trigger which will concat the first_name and last_name to full_name. I have tried below trigger : ``` delimiter | create trigger fullname after insert on user for each row begin update user set full_name=(select concat(first_name,last_name) from user where id=new.id)where id=new.id; end; | ``` It's shows this error while inserting data to user table: `#1442 - Can't update table 'user' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.`