Automating running total calculation with PL/SQL trigger

PL/SQL Trigger: Exercise-9 with Solution

Write a code in PL/SQL to implement a trigger that automatically calculates and updates a running total column for a table whenever new rows are inserted.

Sample Solution:

PL/SQL Code:

-- Create Sales table
CREATE TABLE Sales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount NUMBER,
running_total NUMBER
);

-- Create Trigger
CREATE OR REPLACE TRIGGER Update_Running_Total
BEFORE INSERT ON Sales
FOR EACH ROW
BEGIN
IF :NEW.running_total IS NULL THEN
        SELECT NVL(MAX(running_total), 0) + :NEW.amount
INTO :NEW.running_total
        FROM Sales;
    ELSE
:NEW.running_total := :NEW.running_total + :NEW.amount;
    END IF;
END;
/

Let’s see how the trigger functions

PL/SQL Code:

-- Insert sample data
INSERT INTO Sales (sale_id, sale_date, amount) VALUES (1, TO_DATE('2023-08-01', 'YYYY-MM-DD'), 100);
INSERT INTO Sales (sale_id, sale_date, amount) VALUES (2, TO_DATE('2023-08-02', 'YYYY-MM-DD'), 200);
INSERT INTO Sales (sale_id, sale_date, amount) VALUES (3, TO_DATE('2023-08-03', 'YYYY-MM-DD'), 150);

-- Query the Sales table to see the running total
SELECT * FROM Sales;

Output:

1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
Result Set 1
SALE_ID	SALE_DATE	AMOUNT	RUNNING_TOTAL
1	01-AUG-23	100	100
2	02-AUG-23	200	300
3	03-AUG-23	150	450

Flowchart:

Explanation:

The provided PL/SQL code demonstrates the creation and utilization of a PL/SQL trigger in Oracle Database to facilitate the automatic computation and update of a running total column within a sales table.

A table sales is creates that stores sales-related information, including sale_id, sale_date, amount, and running_total.

A trigger Update_Running_Total operates before an INSERT operation is performed on the Sales table, indicating that it executes prior to the insertion of new rows.

Inside the trigger body, the PL/SQL logic performs dynamic running total calculations. When a new row is inserted, the trigger first examines whether the running_total value of the new row is NULL. If it is, the trigger calculates the running total by retrieving the maximum existing running total from the Sales table and adding the new amount and if not NULL, the trigger simply increments the running total by the amount of the new row.

Previous: Auditing employee table changes with PL/SQL Trigger.
Next: Ensuring order availability with PL/SQL trigger.