INSTEAD OF Triggers
Oracle Fusion / EBS · Technical · PL/SQL
GeneralHigh confidence
Intercepts DML against a non-updatable view and lets you write the actual insert/update/delete logic yourself.
Grounded in: Curated Oracle knowledge layer — PL/SQL
How it works
A view built from a join, or otherwise not 'key-preserved', usually can't be directly inserted into, updated, or deleted from. An INSTEAD OF trigger on that view intercepts the DML entirely and lets you write the real logic yourself — for example, splitting one INSERT against the view into inserts against its underlying base tables — so the view becomes DML-capable even though Oracle can't resolve that automatically.
How it works
CREATE OR REPLACE VIEW emp_dept_v AS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d ON d.department_id = e.department_id;
CREATE OR REPLACE TRIGGER trg_emp_dept_v_insert
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
BEGIN
INSERT INTO employees (employee_id, last_name, department_id)
VALUES (:NEW.employee_id, :NEW.last_name,
(SELECT department_id FROM departments WHERE department_name = :NEW.department_name));
END;
/Related Questions
Have a follow-up, or a different question?
Continue in Ask Oracle AI