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