CODEWITHSUNDEEP
mysqlsqlsql-updatesql-insert
stack
stack

Reputation: 10228

How to determine which column updates?

I have these tables:

// users
+----+--------+--------------+------------+---------------------------+
| id |  name  |    phone     | postalcode |           email           |
+----+--------+--------------+------------+---------------------------+
| 1  | john   | 0338294214   | 65462345   | [email protected]          |
| 2  | jack   | 0657359930   | 93827424   | [email protected]          |
| 3  | peter  | 0083247242   | 99849422   | [email protected]        |
+----+--------+--------------+------------+---------------------------+

// notifications
+----+---------+---------------+------------+
| id | user_id |    content    | timestamp  |
+----+---------+---------------+------------+
| 1  | 2       | phone updated | 1452642595 |
+----+---------+---------------+------------+

Also I have this trigger AFTER UPDATE on users table::

DELIMITER //
CREATE TRIGGER `send_notification` AFTER UPDATE ON `users`
FOR EACH ROW BEGIN
  INSERT INTO notification(user_id, content, timestamp)
                    values(new.id, "phone updated", UNIX_TIMESTAMP())
END
//
DELIMITER

All I'm trying to do is executing that trigger just when phone column updates. (not when name or postalcode or email update). How can I define that condition?

Upvotes: 0

Views: 44

Answers (1)

spencer7593
spencer7593

Reputation: 108490

In a trigger, We can use old and new to reference the values of a column, the value prior to the update, and the new value to be assigned.

Example: 

  IF NOT ( new.phone <=> old.phone ) THEN
    -- value assigned to phone column changed
  ELSE
    -- value of phone column unchanged
  END IF;

The <=> (spaceship operator) is a NULL-safe comparison, which returns TRUE or FALSE even when NULL values are compared.

Upvotes: 2

Related Questions