Fire a trigger after the update of specific columns in MySQL

mysql sql triggers
senior picture senior · Oct 3, 2013 · Viewed 53.8k times · Source

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

Nitu Bansal picture Nitu Bansal · Oct 3, 2013

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 ;

Related questions

MySQL Trigger after update only if row has changed

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count &…

Read More
MySQL Trigger - Storing a SELECT in a variable

I have a trigger in which I want to have a variable that holds an INT I get from a SELECT, so I can use it in two IF statements instead of calling the SELECT twice. How do you declare/…

Read More
MySQL DELIMITER syntax errors

This MySQL script installs multiple triggers. It works on one machine running MySQL 5.0.51b-community. On another machine running MySQL 14.12 Distrib 5.0.45, for redhat-linux-gnu (i386) it fails, with this error message, which seems to be related to the DELIMITER // ... // DELIMITER; syntax : ERROR 1064 (42000) …

Read More