Oracle Trigger

I tried to answer the question of how to prevent the deletion of specific records in a database.

This is needed to ensure that critical records could not be accidentally or intentionally deleted, which might have business or operational implications.

As a sample scenario, I wanted to prevent the deletion of a department record in the DEPARTMENTS table under certain conditions (such as the one with DEPARTMENT_ID = 10).

Creating a Copy of the DEPARTMENTS Table

To implement this solution, I first created a copy of the DEPARTMENTS table and populated it with the existing data from the original table. This step ensured that I could test the functionality of the trigger without affecting the actual data in the production table.

CREATE TABLE DEPARTMENTS_COPY AS SELECT * FROM DEPARTMENTS;
SELECT * FROM DEPARTMENTS_COPY;

        

Query Execution Result

Creating the Trigger

Next, I created an Oracle trigger called prevent_delete_department that fired before any delete operation on the DEPARTMENTS_COPY table. The trigger checked the DEPARTMENT_ID of the record being deleted. If the DEPARTMENT_ID was 10, the trigger would raise a custom error and prevent the deletion from happening.

Trigger Logic

The trigger’s logic was as follows:

• If the DEPARTMENT_ID of the record to be deleted was 10, the deletion was blocked by raising an exception with the message: "Cannot delete department with ID 10".
• If the DEPARTMENT_ID was any value other than 10, the deletion was allowed to proceed.

CREATE OR REPLACE TRIGGER prevent_delete_department
BEFORE DELETE ON DEPARTMENTS_COPY
FOR EACH ROW
BEGIN
    -- If the department ID is 10, raise an exception to prevent deletion
    IF :OLD.DEPARTMENT_ID = 10 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Cannot delete department with ID 10');
    END IF;
END;
/

        

Query Execution Result

Testing the Trigger

After implementing the trigger, I tested it by attempting to delete records from the DEPARTMENTS_COPY table. The following outcomes were observed:

Test 1: Attempting to Delete Department with ID = 10

DELETE FROM DEPARTMENTS_COPY WHERE DEPARTMENT_ID = 10;

        

Query Execution Result

Test 2: Attempting to Delete Department with ID = 20

DELETE FROM DEPARTMENTS_COPY WHERE DEPARTMENT_ID = 20;

        

Query Execution Result