Utilitaires
Scripts
Divers
Jeux
Rechercher
Quillevere.net
Computer science thoughts

Auto-assign the last modification time of a row (PostgreSQL/MySQL)

03/07/2021

Depending on the DBMS, the way to automatically assign the date and time on the modified lines differs.

Whether on MySQL or PostgreSQL, it is the CURRENT_TIMESTAMP function which returns the current time and date. It is also a synonym for NOW(). The precision is on the order of a second (but on PostgreSQL it can be extended to higher precision).

CURRENT_TIMESTAMP on MySQL

The last modification date / time can be configured when creating the table and the field:

CREATE TABLE my_table (
    last_modification_date TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP)

The parameter after DEFAULT indicates the value to be assigned when inserting with INSERT command

The parameter after ON UPDATE indicates the value to be assigned when updating with UPDATE command.

The DBMS automatically manages whether to update the field: if an UPDATE does not modify any field in the row, the last_modification_date field will not be modified either..

CURRENT_TIMESTAMP on PostgreSQL

On PostgreSQL, you must first have created the field using the Timestamp type:

CREATE TABLE my_table (
    last_modification_date TIMESTAMP)

Then go through the use of a stored procedure, called from a trigger.

The stored procedure below will define the field to modify. I voluntarily specified a schema, so that the procedure is attached to this schema and not shared between all.

The 3rd line specifies not to update the date / time until one of the new values ??changes.

CREATE OR REPLACE FUNCTION my_schema.my_function() RETURNS TRIGGER AS $$
BEGIN
    IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) THEN
        NEW.last_modification_date := CURRENT_TIMESTAMP;
        RETURN NEW;
     ELSE
        RETURN OLD;
     END IF;
END;
$$ LANGUAGE plpgsql ;

Then, you have to define the trigger and tell it to launch the function defined previously when a specific table receives a certain event.

The event can be of type BEFORE or AFTER crossed with INSERT, UPDATE or DELETE. It is possible to specify these types at once, by using an OR operator.

In the example below, we ask to update the field before any INSERT or UPDATE.

DROP TRIGGER IF EXISTS my_trigger ON my_schema.my_table ;
CREATE TRIGGER my_trigger
 BEFORE INSERT OR UPDATE
 ON my_schema.my_table
 FOR EACH ROW
 EXECUTE PROCEDURE my_schema.my_function();

Note that the function can be reused to update other tables, as long as they contain the same last_modification_date field.

Dernière modification le 05/07/2021 - Quillevere.net

Search in this website

fr en rss RSS info Informations