CODEWITHSUNDEEP
mysqltriggers
maxhb
maxhb

Reputation: 8845

Trigger for column value change?

I am using some SQL triggers to launch actions when a table row gets modified or created. That works like a charm and in my example sets the column gen_date to the current date/time:

CREATE TRIGGER insert_template BEFORE INSERT ON template
FOR EACH ROW BEGIN
  SET new.gen_date := now();
END;

I have another column image and I would like to add a column image_date which should have the value of the current time/date when that field gets updated.

Question: Is it possible to set up a trigger that keeps track of column wise modifications?

Upvotes: 1

Views: 1083

Answers (1)

Max P.
Max P.

Reputation: 5679

New values are accessible by NEW., old by OLD.. You can compare them to define if values were changed. 

CREATE TRIGGER insert_template BEFORE INSERT ON template
FOR EACH ROW BEGIN
  SET NEW.gen_date := now();
  IF NEW.image <> '' THEN
    SET NEW.image_date := now();
  END IF;
END;

CREATE TRIGGER update_template BEFORE UPDATE ON template
FOR EACH ROW BEGIN
  IF NEW.image <> OLD.image THEN
    SET NEW.image_date := now();
  END IF;
END;

Upvotes: 3

Related Questions