How to have an automatic timestamp update.

The problem

One SQL table has two fields to record the rime of creation and of the last modification. Can the database update these fields automatically when the record is created and updates?

The solution

The table is assumed to have the fields:

CREATE TABLE Users (
  ...,
  created_at DATETIME NOT NULL DEFAULT NOW(),
  modified_at DATETIME,
  ...
);

We have resolved the creation date problem already. Upon creation, created_at will be filled in with the creation date by default, unless another date is passed in.

Now, how can the database update automatically the modified_at field when the field is modified? With an SQL Trigger. If the RDBMS supports triggers, the problem will be solved with code as following:

CREATE TRIGGER TrgUsers_updateTimestamp
  AFTER UPDATE ON Users
    FOR EACH ROW
       SET NEW.modified_at = NOW();

A simple and elegant solution, and thus the applications connecting to the database will not have to worry with timestamps. After any update on any table row, the modified_at field is updated to the current time. This trigger also supports multi-row updates.