w3resource

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.

Next PostgreSQL Exercise: Data Synchronization 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.