In the dynamic world of web development, date and time are significant aspects most especially with data manipulation. In SQL, the `CREATED_AT` and `UPDATED_AT` columns play a vital role in keeping track of when a record was initially added and the last time it was updated. The system effectively maintains these columns automatically. This method is powerful in preserving the integrity of data throughout the system.
CREATED_AT is the timestamp when the record was initially inserted into the database, while UPDATED_AT is the timestamp when any update action was performed on the record. These timestamps serve as a system auditing tool, providing insights into the lifecycle of a record.
Creating the Timestamp Columns in SQL
Creating these timestamp columns involves the SQL data definition language (DDL). The code snippet below creates a new table with `CREATED_AT` and `UPDATED_AT` timestamp columns:
CREATE TABLE sample_table ( id INT PRIMARY KEY, data VARCHAR(100), CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
The `DEFAULT CURRENT_TIMESTAMP` sets the column’s default value to the current date and time. And `ON UPDATE CURRENT_TIMESTAMP` will set the column value to the current date and time whenever any update action is performed on the row.
Post-Creation Column Addition
In many instances, there will be a need to add a `CREATED_AT` and `UPDATED_AT` column post table creation. This can be accomplished using the SQL `ALTER TABLE` statement which modifies an existing table structure.
ALTER TABLE sample_table ADD ( CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
With the above SQL statement, the `CREATED_AT` and `UPDATED_AT` columns are seamlessly added to an already existing table named `sample_table`.
Implementing Triggers
In some database systems like SQLITE, we need to use triggers as an alternative since `ON UPDATE CURRENT_TIMESTAMP` is not supported.
To implement this, we create an `AFTER UPDATE` trigger, which automatically updates the `UPDATED_AT` column upon any modification of the record.
CREATE TRIGGER update_timestamp AFTER UPDATE ON sample_table FOR EACH ROW BEGIN UPDATE sample_table SET UPDATED_AT = CURRENT_TIMESTAMP WHERE id = NEW.id; END;
This trigger is fired after updates on the `sample_table`, setting the `UPDATED_AT` column to the current timestamp.
Implementing CREATED_AT and UPDATED_AT columns in your SQL tables provides a dynamic way to track records from creation to every subsequent update. This form of system auditing strengthens data integrity, ensuring seamless monitoring and maintenance of database records.
