Description:
I am using mysql - ( mysql Ver 14.12 Distrib 5.0.67, for suse-linux-gnu (x86_64) )
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 ;
How to repeat:
mysql> select InsertTime , Col1 from MyTable limit 10;
+---------------------+----------------------+
| InsertTime | Col1|
+---------------------+----------------------+
| 2009-03-06 19:44:31 | NULL |
+---------------------+----------------------+
1 row in set (0.00 sec)
mysql> update MyTable set Col1 =Null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select InsertTime , Col1 from MyTable limit 10;
+---------------------+----------------------+
| InsertTime | InterpretationAuthor |
+---------------------+----------------------+
| 2015-03-16 21:53:04 | NULL |
+---------------------+----------------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
Description: I am using mysql - ( mysql Ver 14.12 Distrib 5.0.67, for suse-linux-gnu (x86_64) ) 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 ; How to repeat: mysql> select InsertTime , Col1 from MyTable limit 10; +---------------------+----------------------+ | InsertTime | Col1| +---------------------+----------------------+ | 2009-03-06 19:44:31 | NULL | +---------------------+----------------------+ 1 row in set (0.00 sec) mysql> update MyTable set Col1 =Null; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select InsertTime , Col1 from MyTable limit 10; +---------------------+----------------------+ | InsertTime | InterpretationAuthor | +---------------------+----------------------+ | 2015-03-16 21:53:04 | NULL | +---------------------+----------------------+ 1 row in set (0.00 sec) --------------------------------------------------------------------------------