1. What is an INSTEAD OF trigger in Oracle?
An INSTEAD OF trigger is a type of trigger in Oracle that executes instead of a DML operation (INSERT, UPDATE, DELETE) on a view.
- Normally, views are read-only if they are complex (involving joins, aggregations).
- An INSTEAD OF trigger allows you to perform DML on the view by defining custom logic for how the changes affect the underlying tables.
Example:
CREATE OR REPLACE TRIGGER instead_of_update_emp
INSTEAD OF UPDATE ON emp_view
FOR EACH ROW
BEGIN
UPDATE employees
SET salary = :NEW.salary
WHERE emp_id = :OLD.emp_id;
END;
/
Here, updating the view emp_view actually updates the employees table.
2. Why use INSTEAD OF triggers instead of regular triggers?
- Regular triggers fire before or after DML operations on tables.
- Views, especially complex ones, cannot be directly updated. INSTEAD OF triggers allow you to handle these updates.
- They let you centralize logic for updating multiple underlying tables via a view.
3. Can INSTEAD OF triggers be used on tables?
No. INSTEAD OF triggers are only allowed on views, not base tables.
- For tables, you use BEFORE or AFTER triggers.
- The logic difference: INSTEAD OF triggers replace the DML operation on a view, while table triggers run alongside the DML.
4. What are the limitations of INSTEAD OF triggers?
- Only allowed on views, not tables.
- Must handle all DML operations you want to support; Oracle won’t do it automatically.
- Cannot call autonomous transactions inside the trigger.
- Cannot be compound triggers (introduced in 11g) because they are view-specific.
5. Can INSTEAD OF triggers be used for DELETE operations?
Yes. You can define INSTEAD OF DELETE triggers.
Example:
CREATE OR REPLACE TRIGGER instead_of_delete_emp
INSTEAD OF DELETE ON emp_view
FOR EACH ROW
BEGIN
DELETE FROM employees WHERE emp_id = :OLD.emp_id;
END;
/
- Deleting from the view will delete rows from the actual table.
6. How does Oracle decide which INSTEAD OF trigger to fire?
- Oracle fires the trigger based on the type of DML operation (INSERT, UPDATE, DELETE) on the view.
- You can have separate INSTEAD OF triggers for each operation.
- The trigger is always row-level, so you use :NEW and :OLD pseudo-records.
7. Can INSTEAD OF triggers work with joins or aggregations in views?
Yes! That’s their main advantage:
- Normally, you cannot update views with joins.
- Using an INSTEAD OF trigger, you can map view changes to multiple tables manually.
Example: Updating a view that combines employees and departments:
CREATE OR REPLACE TRIGGER update_emp_dept
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
UPDATE employees SET salary = :NEW.salary WHERE emp_id = :OLD.emp_id;
UPDATE departments SET dept_name = :NEW.dept_name WHERE dept_id = :OLD.dept_id;
END;
/
8. What are alternatives to INSTEAD OF triggers in Oracle?
If you want to avoid triggers, you can:
- Use Views with DML Rules: Simple views can be updated without triggers.
- Stored Procedures / PL/SQL: Encapsulate insert/update/delete logic in procedures instead of automatic triggers.
- Constraints & Defaults: For simple tasks like enforcing data rules or default values, you don’t need triggers.
- Application-level logic: Handle updates in the application code instead of the database.
9. Are INSTEAD OF triggers good for performance?
- They can add overhead, especially for complex updates across multiple tables.
- The trigger fires row-by-row, which can be slower than set-based updates.
- Always test performance if the view handles large volumes of data.
10. Best practices when using INSTEAD OF triggers
- Keep logic simple and readable.
- Only implement what’s necessary; avoid unnecessary complexity.
- Document the mapping between view columns and table columns clearly.
- Use exception handling inside the trigger to avoid silent failures.
- Combine with stored procedures if multiple views share the same DML logic.