Run Trigger Logic Only on Targeted Column Changes

Conditional Trigger Execution using Stored Procedure

Write a PostgreSQL query to create a trigger that calls a stored procedure only when specific columns are modified in a table.

Solution:

-- Create or replace a function named conditional_update
CREATE OR REPLACE FUNCTION conditional_update() RETURNS TRIGGER AS $$
BEGIN
    -- Check if the new status is different from the old status
    IF NEW.status IS DISTINCT FROM OLD.status THEN
   -- Update the status_log table with the current timestamp where record_id matches the new row's id
        UPDATE status_log SET change_time = NOW() WHERE record_id = NEW.id;
    END IF;
    -- Return the new row after the trigger execution
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger named trg_conditional_update
CREATE TRIGGER trg_conditional_update
-- Set the trigger to execute after updating a row in the tasks table
AFTER UPDATE ON tasks
-- Apply the trigger to each updated row
FOR EACH ROW EXECUTE FUNCTION conditional_update();

Explanation:

• Purpose of the Query:
• The goal is to perform extra processing only when a specific column (status) is modified.
• This demonstrates conditional trigger execution using a stored procedure.
• Key Components:
• The stored procedure compares NEW and OLD values for the status column using IS DISTINCT FROM.
• The trigger is applied on the tasks table for every row update.
• Real-World Application:
• Helps optimize performance by executing additional logic only when necessary.

Notes:

• Ensure that the tasks table contains the status column.
• Conditional logic prevents unnecessary operations.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a trigger that fires only when a set of specific columns have changed in a table.
• Write a PostgreSQL query to create a trigger that executes a stored procedure only if a boolean flag transitions from false to true.
• Write a PostgreSQL query to create a trigger that runs only when an integer column's value crosses a defined threshold.
• Write a PostgreSQL query to create a trigger that conditionally logs modifications when a record meets a multi-part validation condition.

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

Previous PostgreSQL Exercise: Business Rule Enforcement Trigger using Stored Procedure.