Update Record Timestamps Automatically with a Trigger

Timestamp Update Trigger using Stored Procedure

Write a PostgreSQL query to create a trigger that calls a stored procedure to automatically update a timestamp column whenever a record is updated.

Solution:

-- Create or replace a function named update_timestamp that returns a trigger
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    -- Update the last_modified field of the new row to the current timestamp
    NEW.last_modified := NOW();
    -- Return the updated row
    RETURN NEW;
END;
-- Specify the language used in the function as PL/pgSQL
$$ LANGUAGE plpgsql;
-- Create a trigger named trg_update_timestamp to update the timestamp on modifications in my_table
CREATE TRIGGER trg_update_timestamp
-- Trigger before UPDATE operations on my_table
BEFORE UPDATE ON my_table
-- Execute the update_timestamp function for each affected row
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

Explanation:

• Purpose of the Query:
• The goal is to automatically update the last_modified column with the current timestamp upon each update.
• This shows how to integrate a stored procedure within a trigger to maintain audit information.
• Key Components:
• The trigger is defined as a BEFORE UPDATE trigger to modify the NEW record.
• NOW() function is used to fetch the current timestamp.
• Real-World Application:
• Useful in applications that require tracking of record modification times for auditing or synchronization purposes.

Notes:

• The table must include a last_modified column with an appropriate TIMESTAMP data type.
• Ensure that the trigger is applied to the correct table.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a trigger that updates a 'modified_at' column only when specific columns are altered.
• Write a PostgreSQL query to create a trigger that sets a timestamp column using a timezone conversion before every update.
• Write a PostgreSQL query to create a trigger that simultaneously updates two timestamp columns: one for creation and one for modification.
• Write a PostgreSQL query to create a trigger that logs the update timestamp along with the username making the change.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Audit Log Trigger using Stored Procedure.