Views that join tables or compute values often cannot be changed directly: Oracle does not know which base table a change belongs to. An INSTEAD OF trigger tells it. The trigger runs in place of the INSERT, UPDATE, or DELETE on the view, and performs whatever changes on the base tables you decide.
Code for This Guide
The main examples are in the examples/triggers folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create [or replace] trigger name
instead of {insert | update | delete} on view
for each row
begin
... dml on base tables using :old and :new ...
end;INSTEAD OF triggers are always row-level and exist only on views.
Update a Join View
FLIGHT_STATUS_V joins FLIGHTS and ROUTES. The trigger turns an update of the view into an update of the flight's status only, ignoring any other column.
Setup:
create or replace view flight_status_v as select f.flight_id, f.flight_no, r.origin, f.status from flights f join routes r on r.route_id = f.route_id;
Example:
create or replace trigger flight_status_v_trg instead of update on flight_status_v for each row begin update flights set status = :new.status where flight_id = :old.flight_id; end; / update flight_status_v set status = 'CANCELLED', origin = 'XXX' where flight_id = 2800; select flight_id, status from flights where flight_id = 2800; rollback;
Output:
Trigger FLIGHT_STATUS_V_TRG compiled
1 row updated.
FLIGHT_ID STATUS
____________ ____________
2800 CANCELLED
Rollback complete.The UPDATE sets both status and origin through the view, but the trigger changes only FLIGHTS.STATUS, so the route is untouched and only the status is saved. The example rolls back and drops the view afterward.
When to Use Them
- Views over joins, where you want to control which base table each column change goes to.
- Views with aggregates, DISTINCT, or computed columns, which are not updatable at all without a trigger.
- Views used as a stable interface to an application while the tables underneath change.
Things to Know
- The trigger replaces the DML entirely: if it does nothing for a column, changes to that column are silently ignored, as origin was here.
- Validate :NEW values in the trigger, since the view's own WITH CHECK OPTION does not apply to INSTEAD OF triggers.
- One trigger can handle several events with INSERTING, UPDATING, and DELETING.
Related Guides
Conclusion
INSTEAD OF triggers make views updatable by replacing DML on the view with your own DML on its base tables. Use them for join and aggregate views, decide explicitly what each column change means, and validate the new values yourself.
