CODEWITHSUNDEEP
mysqldatabase
GenesisBits
GenesisBits

Reputation: 346

MySQL last_modified column that ignores updates on specific column?

I have a updated_at column that updates the timestamp whenever a change is made on the row. However, I would like it to ignore when changes are made on a specific column, views. Is this possible? The views column is constantly updated and is used as a showcase to see how many times the row has been publicly viewed.

Here is an example table;

CREATE TABLE orders(
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NULL,
  views INT NULL,
  amount FLOAT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY(id)
);

I then have a script that does: views = (views+1) essentially which would then trigger updated_at for that row.

Is it possible to keep updated_at while ignoring changes made on the views column?

Upvotes: 0

Views: 110

Answers (1)

Darma
Darma

Reputation: 113

make a custom trigger with condition is easy solution for this problem, remove ON UPDATE NOW(), and make a trigger to update this column if not view change

check columns with OLD.yourcolumn and NEW.yourcolumn ( sorry Im lazy to write complete solution)

operators will help https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

DELIMITER //
CREATE TRIGGER customTrigger AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
   IF (conditons) THEN
      --type your statements here
   END IF;
END;//
DELIMITER ;

Upvotes: 1

Related Questions