MySQL after update trigger with number of affected rows condition

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.

Answer

Pattern:

CREATE TRIGGER ...
...
BEGIN
INSERT INTO user_log (user_id,action,old_data,new_data)
SELECT NEW.user_id,
       CONCAT('Changed ', OLD.name, "'s ", columnname),
       oldvalue,
       newvalue
FROM ( SELECT 'name' columnname, OLD.name oldvalue, NEW.name newvalue
       UNION ALL
       SELECT 'address', OLD.address, NEW.address
       UNION ALL
       SELECT 'city', OLD.city, NEW.city
       UNION ALL
       SELECT 'phone', OLD.phone, NEW.phone
     ) data
WHERE NOT oldvalue <=> newvalue;       
END;

Also you may use ROW() constructor instead of SELECT .. UNION ALL.

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=c63b122abedf9481d72129bee0d2d87d

Advertisement

Answer