PL/SQL Instead of Triggers

PL/SQL INSTEAD OF Triggers are a special type of trigger used primarily with views to allow operations like INSERT, UPDATE, and DELETE.

These triggers provide a way to perform complex operations that might not be possible directly on a view, enabling you to maintain data integrity and enforce business rules. In this article, We will learn about the INSTEAD OF trigger in PL/SQL by understanding various examples.

INSTEAD OF Trigger in PL/SQL

PL/SQL procedures offer control over database operations, allowing tasks to be executed at specific times. This approach integrates directly into application logic, providing more flexibility than automatic triggers.

Syntax of the INSTEAD OF Trigger:

CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF {INSERT | UPDATE | DELETE}
ON view_name
[FOR EACH ROW]
BEGIN
    EXCEPTION
END;

Parameters:

• trigger_name: The name of the trigger being created (e.g., employee_view_trigger).
• {INSERT | UPDATE | DELETE}: Specifies which type of DML operation the trigger will handle (e.g., INSERT, UPDATE, DELETE).
• view_name: The name of the view on which the trigger is defined (e.g., employee_view).

Using the INSTEAD OF Trigger in PL/SQL

The INSTEAD OF trigger can be used in various PL/SQL operations to filter records. Below are examples demonstrating its use in INSERT, UPDATE, and DELETE statements.

Step 1: First, create the employees and departments tables

CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    department_id NUMBER,
    CONSTRAINT fk_department
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Step 2: Next, insert some data into these tables

INSERT INTO departments (department_id, department_name) VALUES (1, 'HR');
INSERT INTO departments (department_id, department_name) VALUES (2, 'IT');
INSERT INTO departments (department_id, department_name) VALUES (3, 'Finance');

INSERT INTO employees (employee_id, name, department_id) VALUES (101, 'Alice', 1);
INSERT INTO employees (employee_id, name, department_id) VALUES (102, 'Bob', 2);
INSERT INTO employees (employee_id, name, department_id) VALUES (103, 'Charlie', 3);

Step 3: Now, create a view that joins the employees and departments tables

CREATE VIEW employee_details AS
SELECT e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Step 4: To allow INSERT operations on the employee_details view, we create an INSTEAD OF trigger

CREATE OR REPLACE TRIGGER emp_details_insert_trigger
INSTEAD OF INSERT ON employee_details
FOR EACH ROW
BEGIN
   INSERT INTO employees (employee_id, name, department_id)
   VALUES (:NEW.employee_id, :NEW.name,
           (SELECT department_id FROM departments WHERE department_name = :NEW.department_name));
END;

Example 1: Inserting a New Employee into the HR Department

INSERT INTO employee_details (employee_id, name, department_name)
VALUES (104, 'David', 'HR');

Output:

Explanation: The trigger successfully maps 'HR' to department_id 1 and inserts the new employee, David, into the employees table.

Example 2: Inserting a New Employee into the IT Department

INSERT INTO employee_details (employee_id, name, department_name)
VALUES (105, 'Eva', 'IT');

Output:

Explanation: Eva is added to the employees table with the correct department_id for the IT department.

Example 3: Inserting a New Employee into the Finance Department

INSERT INTO employee_details (employee_id, name, department_name)
VALUES (106, 'Frank', 'Finance');

Output:

Explanation: Frank is inserted into the employees table with the corresponding department_id for Finance.

Conclusion

PL/SQL INSTEAD OF triggers are a way to manage changes in database views that normally can’t be updated directly. These triggers let you control on data how you added data, changed, or removed, ensuring everything stays accurate and follows your business rules. By using INSTEAD OF triggers, you can make your database more flexible and work smoothly, even with complex views. This makes it easier to keep your data organized and reliable.