Restricting total order amount using PL/SQL Triggers

PL/SQL Trigger: Exercise-6 with Solution

Write a code in PL/SQL to create a trigger that restricts the insertion of new rows if the total of a column's values exceeds a certain threshold.

Sample Solution:

PL/SQL Code:

-- Create the orders table
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_amount NUMBER
);

-- Create a trigger to restrict total order amount
CREATE OR REPLACE TRIGGER check_order_amount
BEFORE INSERT ON orders
FOR EACH ROW
DECLARE
total_amount NUMBER;
max_threshold NUMBER := 10000; -- Change this to your desired threshold
BEGIN
    -- Calculate the current total order amount for the customer
    SELECT NVL(SUM(order_amount), 0) INTO total_amount
    FROM orders
    WHERE customer_id= :NEW.customer_id;

    -- Check if inserting the new row will exceed the threshold
    IF total_amount+ :NEW.order_amount>max_threshold THEN
        RAISE_APPLICATION_ERROR(-20001, 'Total order amount exceeds the threshold.');
    END IF;
END;
/

Let’s see how the trigger can be functions:

PL/SQL Code:

-- Inserting rows that don't exceed the threshold
INSERT INTO orders (order_id, customer_id, order_amount) VALUES (1, 101, 5000);
INSERT INTO orders (order_id, customer_id, order_amount) VALUES (2, 101, 3000);
INSERT INTO orders (order_id, customer_id, order_amount) VALUES (3, 102, 8000);

-- Attempting to insert a row that would exceed the threshold
-- This should raise an error and prevent the insertion
BEGIN
    INSERT INTO orders (order_id, customer_id, order_amount) VALUES (4, 102, 5000);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Output:

1 row(s) inserted.

1 row(s) inserted.

1 row(s) inserted.

Statement processed.
Error: ORA-20001: Total order amount exceeds the threshold.
ORA-06512: at "SQL_QMRNAIWPSBZXOEKMGGYXAKUGG.CHECK_ORDER_AMOUNT", line 12
ORA-04088: error during execution of trigger 'SQL_QMRNAIWPSBZXOEKMGGYXAKUGG.CHECK_ORDER_AMOUNT'

Flowchart:

Explanation:

The said code in PL/SQL that demonstrates how a PL/SQL trigger that ensures that the total order amount for a customer does not exceed a specified threshold. That can enforce business rules by restricting the insertion of new rows that would violate certain conditions.

A table 'orders' is with columns 'order_id', 'customer_id', and 'order_amount' is involved within this trigger.

A trigger 'check_order_amount' is created using the BEFORE INSERT event that fires before an insertion operation.

It uses two local variables total_amount and max_threshold.

The total_amount variable stores the sum of the existing order amounts for the same customer as the new row and the max_threshold variable holds the threshold value (set to 10000 in the example).

A SELECT statement calculates the current total order amount for the customer.

When an attempt is made to insert a new order with an order amount that would exceed the threshold for a specific customer the trigger fires due to the BEFORE INSERT event and calculates the total order amount for the customer and if the total amount above the threshold, the trigger raises an application error with the code -20001 and displays an error message.

Previous: Preventing duplicate values using PL/SQL triggers.
Next: Implementing salary change auditing trigger in PL/SQL.