w3resource

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.



Follow us on Facebook and Twitter for latest update.