Unable to insert write data conditionaly inside trigger (IF, CONCAT, IFNULL) in MySQL

Question

CREATE TRIGGER TR_Update_Member
AFTER UPDATE ON `member` 
FOR EACH ROW
BEGIN
DECLARE changeNote VARCHAR(5000) DEFAULT '';

SET changeNote = IF(OLD.Name != NEW.Name, 
                    CONCAT( changeNote, 
                            'Name(', 
                            IFNULL(OLD.Name, '--'), 
                            '->', 
                            IFNULL(NEW.Name, '--'), 
                            '), '
                          ), 
                    changeNote);

SELECT TRIM(TRAILING ', ' FROM changeNote) INTO changeNote;

INSERT INTO `member_change_log`(`Name`) VALUES(changeNote)

END

The above trigger does not insert any data when the name contains null. Could anyone please what is wrong with my code.

Akina · Accepted Answer · 2020-05-20 09:15:17Z

1

The above trigger does not insert any data when the name contains null. Could anyone please what is wrong with my code.

If NEW.Name is NULL then OLD.Name != NEW.Name is NULL too, and IF() executes alternative variant, i.e. you obtain

SET changeNote = changeNote

Simply swap variants:

SET changeNote = IF( OLD.Name = NEW.Name, changeNote, CONCAT( ... ) );

If both OLD.Name and NEW.Name may be NULL then use null-safe compare operator <=> instead of regular compare =.

answered May 20, 2020 at 9:15

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to insert write data conditionaly inside trigger (IF, CONCAT, IFNULL) in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related