Automating error notifications with PL/SQL Trigger

PL/SQL Trigger: Exercise-11 with Solution

Design a trigger that sends an email notification to a predefined address whenever an error occurs during a specific operation.

Sample Solution:

PL/SQL Code:

-- Create Employee table
CREATE TABLE Employee (
emp_id NUMBER PRIMARY KEY,
emp_nameVARCHAR2(100),
emp_salary NUMBER
);
-- Create Trigger to simulate sending an email on error
CREATE OR REPLACE TRIGGER Error_Notification_Trigger
AFTER UPDATE ON Employee
FOR EACH ROW
DECLARE
v_error_occurred BOOLEAN := FALSE;
BEGIN
    -- Simulate an error (for testing purposes)
IF :NEW.emp_salary> 100000 THEN
v_error_occurred := TRUE;
    END IF;

    -- If an error occurred, simulate sending an email (replace with actual email logic)
    IF v_error_occurred THEN
        -- Replace this line with actual email sending logic
        DBMS_OUTPUT.PUT_LINE('An error occurred during the update. Sending email notification...');
    END IF;
END;
/

Let's see how the trigger functions

PL/SQL Code:

-- Insert sample data
INSERT INTO Employee (emp_id, emp_name, emp_salary)
VALUES (1, 'John Doe', 80000);
-- Update operation that triggers the simulated error
UPDATE Employee SET emp_salary = 150000 WHERE emp_id = 1;

Output:

1 row(s) inserted.

1 row(s) updated.
An error occurred during the update. Sending email notification...

Flowchart:

Explanation:

The provided PL/SQL code demonstrates the creation and utilization of a PL/SQL trigger in Oracle Database to facilitate the automatic sending of email notifications to a predefined address in the event of an error during a specific operation.

The table employee creates to maintain employee-related information, including emp_id, emp_name, and emp_salary.

The trigger Error_Notification_Trigger operates after an UPDATE operation on the Employee table, indicating that it executes following the completion of updates.

In the code an error is simulated when an employee's salary (emp_salary) exceeds 100000. The condition is checked using the NEW bind variable, which represents the updated row.

If an error occurs, the v_error_occurred variable is set to TRUE.

Subsequently, within the same conditional block, the trigger simulates sending an email notification using the DBMS_OUTPUT.PUT_LINE statement.

Previous: Ensuring order availability with PL/SQL trigger.