Update Information in the underlying tables of a View

• Customer is a mandatory entity → each record in Orders must reference a Customer.
• Orders is an optional entity → a Customer may or may not have any orders.
• You want a single VIEW to update both.

1. Create Base Tables

   CREATE TABLE Customer (
       CustomerID INT PRIMARY KEY,
       Name VARCHAR(100),
       Email VARCHAR(100)
   );
   
   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       CustomerID INT,
       OrderDate DATE,
       Amount DECIMAL(10, 2),
       FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
   );
   

2. Create the View with LEFT JOIN

   Since Orders is optional, we use a LEFT JOIN.

   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;
   

3. Make the View Updatable

   Not all views are inherently updatable. To update both Customer and Orders through the view:

   • The view must not include:
     • Aggregates
     • GROUP BY / HAVING
     • DISTINCT
     • UNION / JOIN (except simple joins — see below)
   • If needed, use INSTEAD OF triggers to enable updates.

4. Using INSTEAD OF TRIGGERS (SQL Server / Oracle)

   For UPDATE on the View:

   CREATE TRIGGER trg_CustomerOrderView_Update
   INSTEAD OF UPDATE ON CustomerOrderView
   FOR EACH ROW
   BEGIN
     -- Update Customer
     UPDATE Customer
     SET Name = :NEW.Name,
         Email = :NEW.Email
     WHERE CustomerID = :OLD.CustomerID;
   
     -- If OrderID exists, update Order
     IF :NEW.OrderID IS NOT NULL THEN
       UPDATE Orders
       SET OrderDate = :NEW.OrderDate,
           Amount = :NEW.Amount
       WHERE OrderID = :OLD.OrderID;
     END IF;
   END;
   

5. Insert Logic

   You can also handle INSERT logic similarly:

   CREATE TRIGGER trg_CustomerOrderView_Insert
   INSTEAD OF INSERT ON CustomerOrderView
   FOR EACH ROW
   BEGIN
     -- Insert into Customer
     INSERT INTO Customer (CustomerID, Name, Email)
     VALUES (:NEW.CustomerID, :NEW.Name, :NEW.Email);
   
     -- If order details are provided, insert into Orders
     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;
   

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID),
    OrderDate DATE,
    Amount DECIMAL(10, 2)
);

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;

CREATE TRIGGER trg_Update_CustomerOrderView
ON CustomerOrderView
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Update Customer Info
    UPDATE c
    SET 
        c.Name = i.Name,
        c.Email = i.Email
    FROM Customer c
    JOIN inserted i ON c.CustomerID = i.CustomerID;

    -- Update Orders Info (if any)
    UPDATE o
    SET 
        o.OrderDate = i.OrderDate,
        o.Amount = i.Amount
    FROM Orders o
    JOIN inserted i ON o.OrderID = i.OrderID
    WHERE i.OrderID IS NOT NULL;
END;

CREATE TRIGGER trg_Insert_CustomerOrderView
ON CustomerOrderView
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert into Customer
    INSERT INTO Customer (CustomerID, Name, Email)
    SELECT CustomerID, Name, Email
    FROM inserted;

    -- Conditionally insert into Orders
    INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount)
    SELECT OrderID, CustomerID, OrderDate, Amount
    FROM inserted
    WHERE OrderID IS NOT NULL;
END;

CREATE TRIGGER trg_Delete_CustomerOrderView
ON CustomerOrderView
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Delete orders first (foreign key dependency)
    DELETE o
    FROM Orders o
    JOIN deleted d ON o.OrderID = d.OrderID;

    -- Then delete customer
    DELETE c
    FROM Customer c
    JOIN deleted d ON c.CustomerID = d.CustomerID;
END;

-- Insert customer only
INSERT INTO CustomerOrderView (CustomerID, Name, Email)
VALUES (1, 'Alice', 'alice@example.com');

-- Insert customer + order
INSERT INTO CustomerOrderView (CustomerID, Name, Email, OrderID, OrderDate, Amount)
VALUES (2, 'Bob', 'bob@example.com', 1001, '2025-06-20', 300.00);

-- Update order and customer info
UPDATE CustomerOrderView
SET Name = 'Robert', Amount = 350.00
WHERE OrderID = 1001;

• SQL Server doesn’t allow direct updates to multi-table views without INSTEAD OF triggers.
• These triggers are powerful but must manually route data to the correct base tables.
• Always validate with SELECT * FROM Customer and SELECT * FROM Orders after testing inserts and updates.

CREATE VIEW
MyView AS
SELECT * FROM 
CustomerInfo, PhoneNumbers 
WHERE
CustomerInfo.CustomerID = PhoneNumbers.CustomerID

Update Information in Underlying Tables

View Implementation, View Update, and Inline Views

SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber
AND Pname='ProductX';

UV1: UPDATEWORKS_ON1
SET Pname = 'ProductY'
WHERE Lname='Smith' AND Fname='John'
AND Pname='ProductX';