INSTEAD OF Trigger in Oracle SQL: Explanation

An INSTEAD OF trigger in Oracle SQL is a type of trigger that overrides the default action of an insert, update, or delete operation on a view or a table. Instead of performing the standard operation (insert, update, delete), it executes a custom action defined within the trigger.

Typically, an INSTEAD OF trigger is used with views, especially when you want to manipulate the underlying data in a specific way that differs from the default behavior of the view.

Key Characteristics:

Used with Views: INSTEAD OF triggers are most commonly used with views. Since views are virtual tables, they don't store data themselves, but show data from base tables. You can't directly insert, update, or delete data in a view, but you can perform these actions on the underlying tables using an INSTEAD OF trigger.

Overriding Action: The trigger defines what action to take instead of the default action for insert, update, or delete.

Syntax:

CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF {INSERT | UPDATE | DELETE}
ON view_name
[FOR EACH ROW]
BEGIN
-- Custom actions (insert/update/delete) for the underlying table
END;

Simple Example:

Suppose you have a view that joins two tables (employees and departments), and you want to allow updates to this view, but when the user tries to update the view, you want to perform custom logic, like updating both the employees and departments tables.

Step 1: Create the tables.

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR2(50),
department_id INT
);

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

Step 2: Create a view combining employees and departments.

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

Step 3: Create an INSTEAD OF trigger to handle updates on the view.

CREATE OR REPLACE TRIGGER emp_dept_update_trigger
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
-- Update the employees table
UPDATE employees
SET name = :NEW.name
WHERE employee_id = :OLD.employee_id;

-- Update the departments table
UPDATE departments
SET department_name = :NEW.department_name
WHERE department_id = :OLD.department_id;

END;

Step 4: Perform an update on the view.

UPDATE emp_dept_view
SET name = 'John Doe', department_name = 'HR'
WHERE employee_id = 101;

In this example:

When you try to update the emp_dept_view, the INSTEAD OF trigger will be invoked.

Instead of directly updating the view, the trigger will handle the updates to both the employees and departments tables as defined.

Analogy for INSTEAD OF Trigger:

Imagine you're in a restaurant, and you order a dish (like a salad). The waiter usually brings you the dish you ordered, but in the case of the INSTEAD OF trigger, instead of bringing the salad as is, the waiter customizes the dish for you. Perhaps the salad comes with a different dressing or additional toppings. The waiter is not following the standard process of just serving the dish; instead, they are performing a custom action behind the scenes.

So, in this analogy:

Order (Trigger Activation): When you make a request (insert, update, delete), the trigger (waiter) intervenes.

Dish (Action): The dish (default action) is replaced with something customized (the action defined in the trigger).

The INSTEAD OF trigger is like the waiter who customizes the dish for you instead of simply fulfilling the standard order.