MySQL after update trigger with number of affected rows condition

[thara]

In MySQL, I want to create a trigger with AFTER UPDATE triggering event for my "user" table.

In next, I have a table named "user_log" which is use to store the modifications that occurred on the parent table "user" after any update commands.

So, data in "user_log" table need be as follows:

select * from user_log;
+--------+---------+----------------------------+---------------+----------------+---------------------+------+
| log_id | user_id | action                     | old_data      | new_data       | changed_date        | read |
+--------+---------+----------------------------+---------------+----------------+---------------------+------+
|      1 |      10 | Changed yyy's name         | yyy           | xxx            | 2022-06-20 14:06:56 | no   |
|      2 |      10 | Changed xxx's address      | No.111,       | No.112,        | 2022-06-20 19:07:38 | no   |
|      3 |      10 | Changed xxx's city         | Old City Name | New City Name  | 2022-06-20 19:07:38 | no   |
|      4 |      10 | Changed xxx's phone number | 011-5000000   | 011-4000000    | 2022-06-20 19:07:38 | no   |
+--------+---------+----------------------------+---------------+----------------+---------------------+------+

As you can see from the data in the table above, it will update several columns at once. So I created my triger as follows, and its working for me.

DELIMITER $$
DROP TRIGGER IF EXISTS `user_log` ;
$$
CREATE TRIGGER `user_log`
  AFTER UPDATE ON `user`
  FOR EACH ROW
BEGIN

  IF OLD.name <> NEW.name THEN
    INSERT INTO user_log (user_id,action,old_data,new_data)
    VALUES(
      NEW.user_id
    , CASE
        WHEN (NEW.name <> OLD.name)
          THEN CONCAT('Changed ', OLD.name, "'s ", 'name')
          ELSE ''
        END
    , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name ELSE '' END
    , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name ELSE '' END  
    );
  END IF;

  IF OLD.address <> NEW.address THEN
    INSERT INTO user_log (user_id,action,old_data,new_data)
    VALUES(
      NEW.user_id
    , CASE
        WHEN (NEW.address <> OLD.address)
          THEN CONCAT('Changed ', OLD.name, "'s ", 'address')    
          ELSE ''
        END
    , CASE WHEN (NEW.address <> OLD.address) THEN OLD.address ELSE '' END
    , CASE WHEN (NEW.address <> OLD.address) THEN NEW.address ELSE '' END  
    );
  END IF;

  IF OLD.city <> NEW.city THEN
    INSERT INTO user_log (user_id,action,old_data,new_data)
    VALUES(
      NEW.user_id
    , CASE
        WHEN (NEW.city <> OLD.city)
          THEN CONCAT('Changed ', OLD.name, "'s ", 'city')   
          ELSE ''
        END
    , CASE WHEN (NEW.city <> OLD.city) THEN OLD.city ELSE '' END
    , CASE WHEN (NEW.city <> OLD.city) THEN NEW.city ELSE '' END  
    );
  END IF;

  IF OLD.phone <> NEW.phone THEN
    INSERT INTO user_log (user_id,action,old_data,new_data)
    VALUES(
      NEW.user_id
    , CASE
        WHEN (NEW.phone <> OLD.phone)
          THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number')    
          ELSE ''
        END
    , CASE WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END
    , CASE WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END  
    );
  END IF;

END$$
DELIMITER ;

My problem is, I have a lot more columns in the user table. Like I said, all columns or several of them are updated at once.

In that case I have to add a large amount of INSERT query to my trigger. So here I would like to know if there is another suitable way to do this.

I also tried it in this way. But its working only for one column.

DROP TRIGGER IF EXISTS `user_log`;
CREATE TRIGGER IF NOT EXISTS `user_log`
  AFTER UPDATE ON user
  FOR EACH ROW
  INSERT INTO user_log (user_id,action,old_data,new_data)
  VALUES (
  NEW.user_id
  , CASE
      WHEN (NEW.name <> OLD.name)
        THEN CONCAT('Changed ', OLD.name, "'s ", 'name')
      WHEN (NEW.address <> OLD.address)
        THEN CONCAT('Changed ', OLD.name, "'s ", 'address')    
      WHEN (NEW.city <> OLD.city)
        THEN CONCAT('Changed ', OLD.name, "'s ", 'city')   
      WHEN (NEW.phone <> OLD.phone)
        THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number')    
      ELSE ''
    END
  , CASE
      WHEN (NEW.name <> OLD.name)
        THEN OLD.name
      WHEN (NEW.address <> OLD.address)
        THEN OLD.address
      WHEN (NEW.city <> OLD.city)
        THEN OLD.city
      WHEN (NEW.phone <> OLD.phone)
        THEN OLD.phone
      ELSE ''
    END

  , CASE
      WHEN (NEW.name <> OLD.name)
        THEN NEW.name
      WHEN (NEW.address <> OLD.address)
        THEN NEW.address
      WHEN (NEW.city <> OLD.city)
        THEN NEW.city
      WHEN (NEW.phone <> OLD.phone)
        THEN NEW.phone
      ELSE ''
    END     
  );

Thank you.  

[requinix]

If you're going to record all the data, wouldn't it be easier to just make the table mirror the user table and stick the old row in there? Or rather, stick the new row in there, and apply the trigger to INSERTs as well.