Using a View to Update the Underlying Tables

A view isn't limited to reporting. Under the right conditions, INSERT, UPDATE, and DELETE statements issued against a view can modify the actual base tables behind it, exactly the way querying the view reads from those same base tables. The previous two lessons already covered when this works cleanly, a single-table view generally supports it directly, and when it doesn't, most multi-table join views fail the standard's updatability test outright. This lesson covers what to do about that second case: how to make a join view writable anyway, deliberately, using Oracle's own mechanism for it.

This isn't a workaround or a hack layered on top of a limitation; it's the intended, documented path for exactly this situation. Oracle's own view mechanism assumes that some views will need writes routed through custom logic, and builds a dedicated trigger type specifically for that purpose rather than leaving developers to improvise something themselves.

Recall the WORKS_ON1 view from an earlier lesson, joining EMPLOYEE, WORKS_ON, and PROJECT. Suppose an update tries to change which project John Smith is credited with, from ProductX to ProductY:

UPDATE WORKS_ON1
SET Pname = 'ProductY'
WHERE Lname = 'Smith' AND Fname = 'John' AND Pname = 'ProductX';

This request is genuinely ambiguous, not just inconvenient. Pname lives on the PROJECT table, so satisfying this update could mean renaming the ProductX project record itself to ProductY, which would affect every employee associated with that project, not just John Smith. Or it could mean leaving PROJECT untouched and instead changing which project John Smith's WORKS_ON row points to. Both interpretations are plausible, they produce different results, and nothing in the statement itself says which one was intended. Oracle has no safe way to guess, which is exactly why a view shaped like this one is read-only by default rather than picking one interpretation arbitrarily.

A second, related kind of ambiguity shows up with aggregation, covered in an earlier lesson via the DEPT_INFO view:

CREATE VIEW DEPT_INFO (Dept_name, No_of_emps, Total_sal)
AS
SELECT Dname, COUNT(*), SUM(Salary)
FROM DEPARTMENT
JOIN EMPLOYEE ON Dnumber = Dno
GROUP BY Dname;

An attempt to UPDATE DEPT_INFO SET Total_sal = 500000 WHERE Dept_name = 'Research' doesn't have two competing interpretations the way WORKS_ON1 did; it has none at all. Total_sal is a sum spread across however many employees happen to belong to the Research department, and there's no single Salary value on any one EMPLOYEE row that this update could sensibly change. This is a different flavor of the same underlying problem: a view built on a join, an aggregate, or both, doesn't preserve a clean one-to-one mapping back to the rows of any single base table, and Oracle refuses to guess at a mapping that may not even exist.

An INSTEAD OF trigger is Oracle's mechanism for exactly this situation. Rather than letting Oracle attempt the write against the view directly, an INSTEAD OF trigger fires in place of that attempt, and its own code explicitly decides which base table each part of the write actually belongs to. The ambiguity from the WORKS_ON1 example doesn't get resolved automatically; a person writing the trigger resolves it once, explicitly, and every subsequent write against the view follows that same resolved logic.

This is worth contrasting with what happens on a view that's genuinely updatable in the standard sense, a single-table view with no aggregation, covered in an earlier lesson. There, Oracle itself already knows exactly which base table row a write maps to, since only one table and one row are involved. An INSTEAD OF trigger exists for the opposite situation: where that mapping genuinely isn't knowable from the view's structure alone, and has to be supplied explicitly instead.

Every trigger built for this purpose needs to be a row-level trigger, FOR EACH ROW, rather than a statement-level trigger that fires once regardless of how many rows a statement affects. This matters mechanically: a statement-level trigger has no way to see the specific column values of any individual row, only that a triggering statement of some kind occurred, which makes it useless for a job that requires reading :new.CustomerID or :new.OrderID for the exact row being written. A row-level trigger fires once per affected row and carries that row's specific values along with it, which is the only way to route each row's data correctly.

Consider two related tables: every Orders row must reference a Customer, but a Customer may have no orders at all.

Building the pieces in order, tables first, then the view, then each trigger, and testing after each addition, follows the same "verify each piece independently" habit already recommended earlier in this course for subqueries and views alike. A trigger with a subtle bug is much easier to isolate when it's the only new piece added since the last thing that was confirmed working.

CREATE TABLE Customer (
    CustomerID NUMBER PRIMARY KEY,
    Name VARCHAR2(100),
    Email VARCHAR2(100)
);

CREATE TABLE Orders (
    OrderID NUMBER PRIMARY KEY,
    CustomerID NUMBER,
    OrderDate DATE,
    Amount NUMBER(10, 2),
    CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Since Orders is optional, the view joining them needs a LEFT JOIN, not an inner join, so customers with no orders still appear:

CREATE VIEW CustomerOrderView AS
SELECT
    c.CustomerID,
    c.Name,
    c.Email,
    o.OrderID,
    o.OrderDate,
    o.Amount
FROM Customer c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

On its own, this view is read-only, for the same structural reason WORKS_ON1 was: it's built on a join. Three INSTEAD OF triggers, one per operation, make it writable by explicitly routing each write to the correct base table.

Handling INSERT:

CREATE OR REPLACE TRIGGER trg_customerorderview_ins
INSTEAD OF INSERT ON CustomerOrderView
FOR EACH ROW
BEGIN
    INSERT INTO Customer (CustomerID, Name, Email)
    VALUES (:new.CustomerID, :new.Name, :new.Email);

    IF :new.OrderID IS NOT NULL THEN
        INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount)
        VALUES (:new.OrderID, :new.CustomerID, :new.OrderDate, :new.Amount);
    END IF;
END;
/

Handling UPDATE:

CREATE OR REPLACE TRIGGER trg_customerorderview_upd
INSTEAD OF UPDATE ON CustomerOrderView
FOR EACH ROW
BEGIN
    UPDATE Customer
    SET Name = :new.Name,
        Email = :new.Email
    WHERE CustomerID = :old.CustomerID;

    IF :new.OrderID IS NOT NULL THEN
        UPDATE Orders
        SET OrderDate = :new.OrderDate,
            Amount = :new.Amount
        WHERE OrderID = :old.OrderID;
    END IF;
END;
/

Handling DELETE:

CREATE OR REPLACE TRIGGER trg_customerorderview_del
INSTEAD OF DELETE ON CustomerOrderView
FOR EACH ROW
BEGIN
    IF :old.OrderID IS NOT NULL THEN
        DELETE FROM Orders WHERE OrderID = :old.OrderID;
    END IF;

    DELETE FROM Customer WHERE CustomerID = :old.CustomerID;
END;
/

The order of operations inside this trigger isn't arbitrary. Orders.CustomerID carries a foreign key referencing Customer.CustomerID, so deleting the order row before the customer row respects that dependency; attempting it the other way around would either fail outright or require the foreign key to permit orphaned references, neither of which is the intended behavior here. Whenever a trigger like this touches more than one table connected by a foreign key, the deletion order needs to mirror that dependency, children before parents, the same principle that governs manual deletes against these tables directly.

:new and :old reference the new and prior values of the row being inserted, updated, or deleted, the standard way an Oracle row-level trigger accesses the data behind the statement that fired it. Each trigger's job is narrow and explicit: figure out which base table a given column actually belongs to, and issue the corresponding INSERT, UPDATE, or DELETE against that table directly.

With all three triggers in place, writes against CustomerOrderView behave as though the view itself were an ordinary table:

-- Insert a customer with no order yet
INSERT INTO CustomerOrderView (CustomerID, Name, Email)
VALUES (1, 'Alice', 'alice@example.com');

-- Insert a customer and an order together
INSERT INTO CustomerOrderView (CustomerID, Name, Email, OrderID, OrderDate, Amount)
VALUES (2, 'Bob', 'bob@example.com', 1001, DATE '2026-06-20', 300.00);

-- Update both the customer's name and the order's amount in one statement
UPDATE CustomerOrderView
SET Name = 'Robert', Amount = 350.00
WHERE OrderID = 1001;

Each of these statements is issued against CustomerOrderView directly, but the INSTEAD OF triggers behind it route every piece to Customer or Orders as appropriate. Confirming the result means checking the base tables themselves:

SELECT * FROM Customer;
SELECT * FROM Orders;

A handful of errors account for most of the trouble building triggers like these for the first time.

Forgetting FOR EACH ROW. Without it, the trigger becomes statement-level and has no access to :new or :old at all, which fails immediately for a trigger whose entire job depends on reading those values.

Assuming :old.OrderID exists on an INSERT. On an INSERT, only :new values are meaningful; there is no prior row to reference, so :old is not the right correlation name inside trg_customerorderview_ins. The examples above use :new consistently for INSERT and both :new/:old for UPDATE and DELETE, matching what's actually available at each stage.

Not handling the optional side of the relationship. Every trigger above checks :new.OrderID IS NOT NULL or :old.OrderID IS NOT NULL before touching Orders, precisely because Orders is optional here. Skipping that check and unconditionally trying to insert or update an Orders row, even when no order data was actually supplied, would either insert a nonsensical row full of nulls or fail outright.

Deleting parent rows before child rows. As covered above with the DELETE trigger, Orders rows have to go before Customer rows because of the foreign key between them. Reversing that order in a more complex multi-table trigger is one of the most common causes of a trigger that works during testing on empty tables and then fails the first time it runs against data with real foreign key relationships.

• Oracle does not allow direct writes against most multi-table views without INSTEAD OF triggers; this isn't a missing feature to work around, it's the deliberate consequence of the ambiguity demonstrated with WORKS_ON1 above.
• The trigger's logic has to manually account for every column being routed, nothing about it is automatic. Adding a new column to CustomerOrderView later means updating the relevant trigger to handle it, not something Oracle infers on its own. If CustomerOrderView later gained a Status column sourced from Orders, every one of the three triggers would need a corresponding update, the INSERT trigger to accept it, the UPDATE trigger to allow changing it, and potentially the DELETE trigger if it affects deletion behavior at all.
• Keep the routing logic inside each trigger as simple as possible. Triggers are powerful, but excessive or overly complex trigger logic can create hard-to-trace interdependencies, especially once a trigger's own actions start firing other triggers in turn. A trigger that itself performs an INSERT or UPDATE against a table with its own triggers defined can set off a chain of cascading effects that becomes genuinely difficult to reason about after a few levels.
• Always verify a write against a view like this by checking the underlying base tables directly afterward, SELECT * FROM Customer and SELECT * FROM Orders in this example, rather than assuming the trigger routed everything correctly just because the statement against the view succeeded. A trigger with a logic error can still let the outer INSERT or UPDATE statement complete without raising an error, while quietly writing the wrong data, or no data at all, to one of the base tables.
• Document what each trigger does and why, directly alongside its code. A reader encountering trg_customerorderview_upd for the first time has no way to know, just from the trigger's name, that it silently skips updating Orders whenever OrderID is null; that behavior needs to be visible somewhere nearby, not left as an implicit assumption buried in an IF statement.

Looking Ahead

A join view's default read-only status isn't an arbitrary restriction; it reflects a genuine ambiguity about which base table a given write actually belongs to, the same ambiguity WORKS_ON1 demonstrated concretely. INSTEAD OF triggers resolve that ambiguity explicitly, once, by defining exactly how a write against the view maps onto the base tables underneath it.

A few points worth carrying forward from this lesson:

• A join view is read-only by default because a combined row often has no single, unambiguous underlying row to write a change back to, whether that ambiguity comes from a multi-table join or from aggregation collapsing many rows into one.
• INSTEAD OF triggers replace Oracle's default handling of a write against a view with explicit, hand-written logic that routes each part of the write to its correct base table.
• Every INSTEAD OF trigger built for this purpose needs to be a row-level trigger, since only a row-level trigger has access to the specific column values, via :new and :old, that the routing logic depends on.
• Deletion order inside a multi-table trigger has to respect foreign key dependencies, children before parents, exactly as it would for manual deletes against the same tables.
• Testing a view built this way means verifying the base tables directly, not just confirming that the statement against the view completed without error.