Before Insert Trigger not working as defined-CodePudding

I am writing mysql before insert trigger but its not working. Mysql is giving no errors but column value is not updated.

BEGIN
set NEW.message_state = CASE
WHEN New.user_id = '11' THEN 'Visible'
ELSE New.message_state
END

CodePudding user response：

Your example gives an syntax error.

Example solution with case

BEGIN
    CASE WHEN NEW.user_id = '11' THEN 
      SET NEW.message_state = 'Visible';
    ELSE   
      BEGIN 
      END;      
    END CASE;  
END

ELSE statement is needed, because in case of user_id <> 11 MYSQL will try to find next WHEN or ELSE clausule, and will throw error if there is none.

Example solution with second CASE

BEGIN
    SET NEW.message_state = (select 
           CASE WHEN NEW.user_id = '11' THEN 'Visible' 
                ELSE NEW.message_state 
           END);  
END

But this syntax is quite overcomplitaced for what you try to achieve. Simple IF will be enough

BEGIN
    IF NEW.user_id = '11' THEN
       SET NEW.message_state = 'Invisible';
    END IF; 
END;

DB Fiddle example

CodePudding user response：

try

BEGIN
set NEW.message_state = CASE
WHEN New.user_id = '11' THEN 'Visible'
ELSE New.message_state=New.message_state
END

or remove the else

BEGIN
set NEW.message_state = CASE
WHEN New.user_id = '11' THEN 'Visible'
END