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).
The last modification date / time can be configured when creating the table and the field:
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..
On PostgreSQL, you must first have created the field using the Timestamp type:
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.
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.
Note that the function can be reused to update other tables, as long as they contain the same last_modification_date field.