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
CodePudding user response:
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;
CodePudding user response:
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.