Search code examples
sqlmysqltriggers

MySQL trigger on update set new value to old value from another column


I have code:

CREATE TRIGGER update_after_requirement_on_description
BEFORE UPDATE
ON requirement FOR EACH ROW
BEGIN
    IF !(NEW.description <=> OLD.description) THEN
        UPDATE requirement
        SET NEW.description_previous = OLD.description;
    END IF;
END;

and second one:

CREATE TRIGGER update_after_requirement_on_description
BEFORE UPDATE
ON requirement FOR EACH ROW
IF !(NEW.description <=> OLD.description) THEN
    UPDATE requirement
    SET NEW.description_previous = OLD.description;
END IF;

both return result from db:

#1064 - Something is wrong in your syntax near '' in line: "SET NEW.description_previous = OLD.description;"

what's wrong ?

thanks

EDIT:

this version works:

DELIMITER $$
CREATE TRIGGER update_after_requirement_on_description
BEFORE UPDATE
ON requirement FOR EACH ROW
    IF !(NEW.description <=> OLD.description) THEN
        SET NEW.description_previous = OLD.description;
    END IF;
$$
DELIMITER ;

Solution

  • Simply

    CREATE TRIGGER update_after_requirement_on_description 
    BEFORE UPDATE ON requirement 
    FOR EACH ROW 
    SET NEW.description_previous = CASE WHEN !(NEW.description <=> OLD.description) 
                                        THEN OLD.description 
                                        ELSE NEW.description_previous 
                                        END;
    

    And you do not need in DELIMITER.