Auto-assign the last modification time of a row (PostgreSQL/MySQL)
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:
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:
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.
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.
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.
Commentaires
Search in this website
  |  RSS |  Informations |