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

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

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0 
    CREATE TRIGGER increment_engage
BEFORE UPDATE ON engage_step_user_response
FOR EACH ROW
SET NEW.retakes = OLD.retakes + NOT (NEW.response_json <=> OLD.response_json);

    the code is null-safe.

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

Back To Top
Search