MySQL after/before update on 'table' set last update on the same 'table'

Question

This is my table 'employees':

+----------------+--------------+------+-----+-------------------+-------+
| Field          | Type         | Null | Key | Default           | Extra |
+----------------+--------------+------+-----+-------------------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL              |       |
| lastName       | varchar(50)  | NO   |     | NULL              |       |
| firstName      | varchar(50)  | NO   |     | NULL              |       |
| extension      | varchar(10)  | NO   |     | NULL              |       |
| email          | varchar(100) | NO   |     | NULL              |       |
| officeCode     | varchar(10)  | NO   |     | NULL              |       |
| reportsTo      | int(11)      | YES  |     | NULL              |       |
| jobTitle       | varchar(50)  | NO   |     | NULL              |       |
| age            | int(11)      | YES  |     | NULL              |       |
| lastUpdate     | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+----------------+--------------+------+-----+-------------------+-------+

I'm starting to learn triggers in MySQL. The idea is that when I update an employee I want to set the current day-time in lastUpdate So I tried to do a simple one:

DELIMITER $$
CREATE TRIGGER lastUpdate_employee
BEFORE UPDATE
   ON employees FOR EACH ROW
BEGIN
    SET new.lastUpdate = CURRENT_TIMESTAMP;
END; 
$$
DELIMITER ;

I can create the trigger without errors, but when I try to update employees it gives me an error:

ERROR 1442 (HY000): Can't update table 'employees' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I also don't know if I should use AFTER UPDATE or BEFORE UPDATE.

Sorry if it's a basic error but I don't know how to solve this. Thank you.

Maybe you'll find this useful: https://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already — Kamran Poladov, Sep 09 '18 at 17:25

dognose · Accepted Answer · 2018-09-09T17:52:26.130

For this task, you can alter your CREATE TABLE Statement as well, and define the column to be automatically updated with every "update" of any other column:

(Note the ON UPDATE CURRENT_TIMESTAMP part)

CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `lastChange` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `text` varchar(200) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Of course, you can alter existing tables as well:

ALTER TABLE test
  MODIFY COLUMN lastChange
  TIMESTAMP
  NOT NULL 
  DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP;

Beside this, your trigger looks fine for me.

Please ensure, that before creating your trigger, you add a drop trigger if exists statement. Else a "prior" Version of your trigger might exist, so your updated trigger wasn't created as expected.

MySQL after/before update on 'table' set last update on the same 'table'

1 Answers1