Updating DATETIME and TIMESTAMP columns automatically

If our database has a DATETIME or TIMESTAMP column that we want to be updated whenever the record is updated, there is no need for us to put that logic in our application. MariaDB can take care of it for us.

How to do it...

1. Launch the mysql command-line client application and connect to our MariaDB server.

2. Create a test database if it doesn't already exist and switch to it using the following command:

   CREATE DATABASE IF NOT EXISTS test;
   USE test;
   

3. Create a simple table named dtts using the following commands:

   CREATE TABLE dtts ( 
     id int(11) NOT NULL AUTO_INCREMENT, 
     name varchar(25), 
     dt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), 
     ts timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) 
       ON UPDATE CURRENT_TIMESTAMP(3), 
     PRIMARY KEY (id) 
   );
   

4. Insert some data into our new table using the INSERT command:

   INSERT INTO dtts (name) VALUES 
     ('Thomass'),('Gordon'),('Howard'),('Ezra'); 
   

5. Fix the misspelling of Thomas:

   UPDATE dtts...