Instead of triggers in oracle database are defined on views only. A normal DML trigger executes only when a DML operation is issued on a table. Whereas instead of trigger fires when a DML statment is issued on the view. Instead-of triggers must be row level.
Create Instead of Trigger - Example:
Take a look at the following view defintion:
As the view consists of two table joins, it is illegal to insert records into this view as the insert requires both the underlying tables to be modified.
By creating an instead-of trigger on the view, you can insert the records into both the underlying tables. An examples is shown below:
Create Instead of Trigger - Example:
Take a look at the following view defintion:
Create or replace view emp_dept_join as
Select d.department_id,
d.department_name,
e.first_name,
e.last_name
from employees e,
departments d
where e.department_id = d.department_id;
As the view consists of two table joins, it is illegal to insert records into this view as the insert requires both the underlying tables to be modified.
By creating an instead-of trigger on the view, you can insert the records into both the underlying tables. An examples is shown below:
CREATE OR REPLACE TRIGGER insert_emp_dept
INSTEAD OF INSERT ON emp_dept_join
DECLARE
v_department_id departments.department_id%TYPE;
BEGIN
BEGIN
SELECT department_id INTO v_department_id
FROM departments
WHERE department_id = :new.department_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO departments (department_id, department_name)
VALUES (dept_sequence.nextval, :new.department_name)
RETURNING ID INTO v_department_id;
END;
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES(emp_sequence.nextval, :new.first_name, :new.last_name, v_department_id);
END insert_emp_dept;
/
0 comments