How to create a trigger to increment a column when that row only one column is updated?

Question

I need to write a SQL trigger whenever (engage_step_user_response) table (response_json) column updated, I need to increase no of retakes by one in the retakes column. I have tried to write a SQL trigger referencing some details. But it doesn't succeed. can anyone please help regarding this?

CREATE TRIGGER increment_engage
    ON engage_step_user_response
    AFTER UPDATE
AS 
     IF UPDATE(response_json)
FOR EACH ROW
SET NEW.retakes = OLD.retakes + 1

I'm expecting whenever the response_json column is updated retakes must be increase by one.enter image description here

Answer 1

Try this :

You will need a BEFORE UPDATE trigger if you want to modify the data:

CREATE TRIGGER increment_engage BEFORE UPDATE ON engage_step_user_response 
FOR EACH ROW     
BEGIN

   IF !(NEW.response_json <=> OLD.response_json) THEN
       SET NEW.retakes = NEW.retakes+1;
   END IF;
end;

demo here