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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics