Description:
I have a table "MyTable", that has three columns - a column "Col1" of type "varchar" and two columns "UpdateTime" and "InsertTime" of type "timestamp"
I want to use InsertTime to track time of record insertion and UpdateTime to track time of record update.
mysql> describe MyTable;
| Field | Type | Null | Key | Default |
+-----------------------+--------------+------+-----+---------------------+
|Col1 | varchar(64) | YES | | NULL |
|InsertTime | timestamp | NO | | CURRENT_TIMESTAMP
|UpdateTime | timestamp | NO | | 0000-00-00 00:00:00
I have a trigger definition as follows that is expected to ensure that when MyTable is updated the InsertTime timestamp will be preserved to original creation one , but the UpdateTime timestamp will be updated to current time stamp
DELIMITER $$
DROP TRIGGER IF EXISTS `MyTable_UPDATE`; $$
CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON MyTable
FOR EACH ROW
BEGIN
SET NEW.`UpdateTime` = CURRENT_TIMESTAMP ;
SET NEW.`InsertTime` = OLD.`InsertTime`;
END $$
DELIMITER ;
The problem is when I update "MyTable" as follows
update MyTable set Col1=NULL ;
Then the InsertTime value is also getting set to CURRENT_TIMESTAMP along with UpdateTime . This problem goes away ( It preserves InsertTime ) if I change the trigger definition to as follows ( by getting rid of assignment to UpdateTime - which is not what I want)
DELIMITER $$
DROP TRIGGER IF EXISTS `MyTable_UPDATE`; $$
CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON MyTable
FOR EACH ROW
BEGIN
# SET NEW.`UpdateTime` = CURRENT_TIMESTAMP ;
# -- THIS DOES NOT HELP ME I WANT UpdateTime to be updated
SET NEW.`InsertTime` = OLD.`InsertTime`;
END $$
DELIMITER ;
Is this a BUG or is this expected behavior. How can I get this to work ?
mysql> select InsertTime , InterpretationAuthor from Study;
+---------------------+----------------------+
| InsertTime | InterpretationAuthor |
+---------------------+----------------------+
| 2015-03-16 09:27:15 | NULL |
| 2015-03-16 09:27:15 | NULL |
| 2015-03-16 09:27:45 | NULL |
+---------------------+----------------------+
3 rows in set (0.00 sec)
mysql> update Study set InterpretationAuthor=NULL;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select InsertTime , InterpretationAuthor from Study;
+---------------------+----------------------+
| InsertTime | InterpretationAuthor |
+---------------------+----------------------+
| 2015-03-16 09:39:36 | NULL | -- Note that insert time changed owing to trigger incorrect behavior
| 2015-03-16 09:39:36 | NULL |
| 2015-03-16 09:39:36 | NULL |
+---------------------+----------------------+
3 rows in set (0.00 sec)
How to repeat:
-do-
Description: I have a table "MyTable", that has three columns - a column "Col1" of type "varchar" and two columns "UpdateTime" and "InsertTime" of type "timestamp" I want to use InsertTime to track time of record insertion and UpdateTime to track time of record update. mysql> describe MyTable; | Field | Type | Null | Key | Default | +-----------------------+--------------+------+-----+---------------------+ |Col1 | varchar(64) | YES | | NULL | |InsertTime | timestamp | NO | | CURRENT_TIMESTAMP |UpdateTime | timestamp | NO | | 0000-00-00 00:00:00 I have a trigger definition as follows that is expected to ensure that when MyTable is updated the InsertTime timestamp will be preserved to original creation one , but the UpdateTime timestamp will be updated to current time stamp DELIMITER $$ DROP TRIGGER IF EXISTS `MyTable_UPDATE`; $$ CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON MyTable FOR EACH ROW BEGIN SET NEW.`UpdateTime` = CURRENT_TIMESTAMP ; SET NEW.`InsertTime` = OLD.`InsertTime`; END $$ DELIMITER ; The problem is when I update "MyTable" as follows update MyTable set Col1=NULL ; Then the InsertTime value is also getting set to CURRENT_TIMESTAMP along with UpdateTime . This problem goes away ( It preserves InsertTime ) if I change the trigger definition to as follows ( by getting rid of assignment to UpdateTime - which is not what I want) DELIMITER $$ DROP TRIGGER IF EXISTS `MyTable_UPDATE`; $$ CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON MyTable FOR EACH ROW BEGIN # SET NEW.`UpdateTime` = CURRENT_TIMESTAMP ; # -- THIS DOES NOT HELP ME I WANT UpdateTime to be updated SET NEW.`InsertTime` = OLD.`InsertTime`; END $$ DELIMITER ; Is this a BUG or is this expected behavior. How can I get this to work ? mysql> select InsertTime , InterpretationAuthor from Study; +---------------------+----------------------+ | InsertTime | InterpretationAuthor | +---------------------+----------------------+ | 2015-03-16 09:27:15 | NULL | | 2015-03-16 09:27:15 | NULL | | 2015-03-16 09:27:45 | NULL | +---------------------+----------------------+ 3 rows in set (0.00 sec) mysql> update Study set InterpretationAuthor=NULL; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select InsertTime , InterpretationAuthor from Study; +---------------------+----------------------+ | InsertTime | InterpretationAuthor | +---------------------+----------------------+ | 2015-03-16 09:39:36 | NULL | -- Note that insert time changed owing to trigger incorrect behavior | 2015-03-16 09:39:36 | NULL | | 2015-03-16 09:39:36 | NULL | +---------------------+----------------------+ 3 rows in set (0.00 sec) How to repeat: -do-