CODEWITHSUNDEEP
mysqlsqltriggers
senior
senior

Reputation: 2266

Fire a trigger after the update of specific columns in MySQL

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?

Upvotes: 44

Views: 75949

Answers (4)

Ayan Bose
Ayan Bose

Reputation: 1

DELIMITER //

CREATE TRIGGER myTrigger AFTER UPDATE ON myTable

FOR EACH ROW

BEGIN

   IF ((NEW.column1 <> OLD.column1) or (IFNULL(NEW.column1, '') <> IFNULL(OLD.column1, ''))) THEN

      --type your statements here

   END IF;

END;//

DELIMITER ;

Upvotes: 0

Nitu Bansal
Nitu Bansal

Reputation: 3856

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 ;

Upvotes: 74

senior
senior

Reputation: 2266

As a solution, I did like that:

CREATE  TRIGGER  myTrigger
AFTER UPDATE  ON myTable
FOR EACH ROW
BEGIN
IF NEW.myColumn1 <> OLD.myColumn1 || NEW.myColumn2 <> OLD.myColumn2
then

.....

END IF
END

Upvotes: 6

juergen d
juergen d

Reputation: 204756

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 ...

Upvotes: 7

Related Questions