Mysql Question posted in
The official MySQL documentation can be found here.

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 ;
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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;

    by @Akina


  2. 0

    why « UPDATE requirement» before set ?
    just use set :

    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;
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search