Fire a trigger after the update of specific columns in MySQL

Question

In MySQL, I want to fire a trigger after the update of specific columns.

I know how to do it in Oracle and DB2:

CREATE  TRIGGER  myTrigger AFTER UPDATE of myColumn1,myColumn2 ... ON myTable FOR EACH ROW BEGIN .... END 

How to do that with MySQL?

Answer 1

You can't trigger on a particular column update in SQL. It is applied on a row.

You can put your condition for columm in your trigger with an IF statement, as below:

DELIMITER // CREATE TRIGGER myTrigger AFTER UPDATE ON myTable FOR EACH ROW BEGIN    IF !(NEW.column1 <=> OLD.column1) THEN       --type your statements here    END IF; END;// DELIMITER ; 

Answer 2

You can't specify only to fire on specific column changes. But for a record change on a table you can do

delimiter | CREATE  TRIGGER  myTrigger AFTER UPDATE ON myTable FOR EACH ROW BEGIN    ... END | delimiter ; 

In your trigger you can refer to old and new content of a column like this

if NEW.column1 <> OLD.column1 ...