3

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.

John Woo
  • 258,903
  • 69
  • 498
  • 492
Harjeet Jadeja
  • 1,594
  • 4
  • 19
  • 39
  • Check this: http://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert – mavili Mar 16 '13 at 05:41

2 Answers2

7

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 ;
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • oops my mistake this logic of creating trigger before inserting record didn't strike in my mind....its working :) thnk you sir...!!! – Harjeet Jadeja Mar 16 '13 at 05:52
2

For your case, I would like to suggest you to use computed column instead of trigger, because trigger you need to create for insert/update;

Hope this can help you. 


    create table [users] 
    ( id int identity(1,1),
      First_Name varchar(100),
      Last_Name varchar(100),
      Full_Name as concat(First_Name,' ', Last_Name) persisted
   )
      Insert into [users]
      select 'Bob', 'Ryan'


     select * from [users]

    update users 
    set First_Name = 'Michael'
    where id=1

    select * from users
ljh
  • 2,546
  • 1
  • 14
  • 20