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.