Trigger Notifications for Critical Data updates
Notification Trigger using Stored Procedure
Write a PostgreSQL query to create a trigger that calls a stored procedure to send notifications when critical updates occur in a table.
Solution:
-- Define or replace a function named send_notification that returns a trigger
CREATE OR REPLACE FUNCTION send_notification() RETURNS TRIGGER AS $$
-- Begin the function block
BEGIN
-- Execute the notify_user function with a message containing the critical record's id
PERFORM notify_user('Critical update on record ' || NEW.id);
-- Return the new row after processing the trigger
RETURN NEW;
-- End the function block
END;
$$ LANGUAGE plpgsql;
-- Create a trigger named trg_send_notification for critical updates in critical_table
CREATE TRIGGER trg_send_notification
-- Specify that the trigger fires after an update on the critical_table
AFTER UPDATE ON critical_table
-- Set the trigger to execute for each row only when the new critical_flag is TRUE
FOR EACH ROW WHEN (NEW.critical_flag = TRUE)
-- Execute the send_notification function when the trigger condition is met
EXECUTE FUNCTION send_notification();
Explanation:
- Purpose of the Query:
- The goal is to send a notification when a record marked as critical is updated.
- This demonstrates how to conditionally execute a stored procedure using a trigger.
- Key Components:
- The WHEN clause ensures the trigger fires only when the critical_flag is TRUE.
- The stored procedure calls an assumed notification function.
- Real-World Application:
- Useful in alerting systems where immediate action is required for critical data changes.
Notes:
- Ensure that the notify_user function exists and is correctly implemented.
- The critical_table must include a critical_flag column for conditional triggering.
For more Practice: Solve these Related Problems:
- Write a PostgreSQL query to create a trigger that sends a notification when a critical field's value exceeds a specific threshold.
- Write a PostgreSQL query to create a trigger that sends an email alert via an external function upon inserting a critical record.
- Write a PostgreSQL query to create a trigger that logs an alert into a notifications table when multiple fields change simultaneously.
- Write a PostgreSQL query to create a trigger that conditionally calls a notification procedure only when complex business rules are met.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous PostgreSQL Exercise: Archival 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