Restricting column updates during specific hours using PL/SQL triggers

PL/SQL Trigger: Exercise-2 with Solution

Write a code in PL/SQL to create a trigger that prevents updates on a certain column during specific hours of the day.

Sample Solution:

PL/SQL Code:

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

-- Create a trigger to prevent updates during specific hours
CREATE OR REPLACE TRIGGER prevent_updates
BEFORE UPDATE OF order_amount ON orders
FOR EACH ROW
DECLARE
current_hour NUMBER;
BEGIN
    -- Get the current hour
    SELECT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'HH24')) INTO current_hour FROM DUAL;

    -- Check if it's outside of business hours (9 AM to 5 PM)
    IF current_hour< 9 OR current_hour>= 17 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Updates are not allowed during non-business hours.');
    END IF;
END;
/

Let's see how the trigger functions:

PL/SQL Code:

-- Insert a sample order
INSERT INTO orders (order_id, order_date, order_amount) VALUES (1, SYSTIMESTAMP, 1000);

-- Update the order amount during business hours
UPDATE orders SET order_amount = 1500 WHERE order_id = 1;

-- Update the order amount during non-business hours
UPDATE orders SET order_amount = 2000 WHERE order_id = 1;

Output:

1 row(s) inserted. 
ORA-20001: Updates are not allowed during non-business hours. ORA-06512: at "SQL_XWPLBLJQVQMCLONNGGTSSIRWZ.PREVENT_UPDATES", line 9 ORA-06512: at "SYS.DBMS_SQL", line 1721 
ORA-20001: Updates are not allowed during non-business hours. ORA-06512: at "SQL_XWPLBLJQVQMCLONNGGTSSIRWZ.PREVENT_UPDATES", line 9 ORA-06512: at "SYS.DBMS_SQL", line 1721

Flowchart:

Explanation:

The said code in PL/SQL that demonstrates how to utilize a PL/SQL trigger to enforce data integrity rules by restricting updates on a specific column of a table during specific hours of the day.

Assume a table orders with columns order_id, order_date, and order_amount.

A trigger named prevent_updates is created using the BEFORE UPDATE that fires before an UPDATE operation is executed on the order_amount column of the orders table.

Within the trigger's PL/SQL block, the current hour of the system time is retrieved using the SYSTIMESTAMP function and converted to a numerical value using TO_NUMBER and TO_CHAR functions.

The trigger then checks whether the current hour falls within business hours (9 AM to 5 PM) and if not an application error is raised using RAISE_APPLICATION_ERROR. This prevents the update from proceeding.

An UPDATE statement is executed to attempt to change the order_amount of an order during non-business hours. The trigger prevents this update from taking place by raising an application error.

Previous:Automatically Updating Timestamps with PL/SQL Triggers.
Next: Tracking Deleted Rows with a PL/SQL Trigger.