CODEWITHSUNDEEP
mysqltriggers
Devika V
Devika V

Reputation: 658

Mysql update trigger except for specific column updates

In MySQL, I want to fire a trigger when update on all columns except one column update.

In my table row I have 40 columns. I want trigger to update the column update_time whenever there is an update happens on any field except update_time field.

CREATE TRIGGER `UpdateDateTrigger`
BEFORE UPDATE ON `users`
FOR EACH ROW 
    IF NOT UPDATE(`update_time`) BEGIN
        SET new.update_timestamp = now()
    END

But it is not working as expected.

Upvotes: 0

Views: 2643

Answers (2)

Reaz Murshed
Reaz Murshed

Reputation: 24251

Instead of having a trigger function I would like to suggest you to create the table wisely so that the update_time field gets updated automatically when something is changed associated with that row.

Please look into the documentation here for automatic update on time for each update of the row. The create syntax is simple and effective. 

CREATE TABLE t1 (
  // .... Other columns
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

If you want a specific control over each of your column, then writing a trigger is the best idea I think. Please check the answer here.

Upvotes: 1

Devika V
Devika V

Reputation: 658

By looking at MySQL automatic update for specific column instead of whole record , I've got the solution to exclude two columns update in trigger

IF !((NEW.last_visited <> OLD.last_visited) || 
(NEW.update_time <> OLD.update_time)) THEN
   SET new.update_time = now();
END IF

Thank you!

Upvotes: 3

Related Questions