CODEWITHSUNDEEP
mysqlmariadbdatabase-trigger
yarek
yarek

Reputation: 12054

mysql trigger on update: how to detect NULL values?

I try to update the porcentageComplete profil when users updates his profile:

statut field: 

`statut` enum('Marié','En couple','Divorcé') DEFAULT NULL,

Trigger is: 

DROP TRIGGER IF EXISTS update_user;
DELIMITER $$

CREATE TRIGGER update_user
    BEFORE UPDATE
    ON users FOR EACH ROW
BEGIN
    DECLARE porcentage int default 0;
    IF NEW.statut!=NULL OR OLD.statut!=NULL THEN 
        set porcentage = porcentage + 1;
    END IF;
    SET NEW.porcentageCompleted = porcentage;

END$$

This not update correctly the porcentageCompleted : it looks like NULL comparaison does not work correctly

Upvotes: 0

Views: 55

Answers (1)

sticky bit
sticky bit

Reputation: 37482

Comparisons with = or <>(or !=) to a NULL value yield NULL themselves, so not true, i.e. false. Use IS NULL and IS NOT NULL to check for NULLs.

...
IF NEW.statut IS NOT NULL
    OR OLD.statut IS NOT NULL THEN 
...

Worth a read: 3.3.4.6 Working with NULL Values

Upvotes: 2

Related Questions