Search code examples
phpmysqlpdoalter-table

PDO query updating a datetime column not in query Part 2

Continuation from this question:

PDO query updating a datetime column not in query

A column in my table called lastLoginDate was being automatically updated even though my prepared statement did not include said column.

Apparently, when I created the new column, a trigger was set.

Upon using the command SHOW CREATE TABLE table_name, I returned the following results:

CREATE TABLE `users_edi` (
`username` varchar(30) NOT NULL DEFAULT '',
`fullname` varchar(50) DEFAULT NULL,
`userlevel` tinyint(1) unsigned NOT NULL,
`ipaddress` varchar(30) DEFAULT NULL,
`email` varchar(150) DEFAULT NULL,
`entrydate` datetime DEFAULT NULL,
`division` varchar(35) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
`userid` varchar(32) DEFAULT NULL,
`timestamp` int(11) unsigned NOT NULL,
`job_title` varchar(30) DEFAULT NULL,
`dept` varchar(50) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
`lastLoginDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, // <-- here
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The table is years old. I just created the column and somehow, a trigger was set to it (I guess).

Regardless, I tried to remove it using the following command:

ALTER TABLE `users_edi` 
  `lastLoginDate` datetime DEFAULT NULL

But I only get the following error:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`lastLoginDate` datetime DEFAULT NULL' at line 4

How do I remove this trigger using the ALTER TABLE command or any other command?

Solution 
  • ALTER TABLE users_edi MODIFY COLUMN lastLoginDate DATETIME DEFAULT NULL;

    You might like to read this page on ALTER TABLE: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html