w3resource

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.

Next PostgreSQL Exercise: Cascade Update Trigger using Stored Procedure.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.