title: How to INSERT data using CREATE VIEW object with multiple tables using INSTEAD OF trigger in Oracle?
body: <p>Based on feedback from an stackoverflow user I researched docs.oracle.com about INSTEAD OF tirggers, but I still can't get this right. The view creates fine, but the INSTEAD OF trigger doesn't. Moreover, I don't understand how to set 'n' for multiple inputs. After I execute the code there is no indication of any error or if it worked. I loose the SQL> prompt and can't do anything, ending up having to restart the environment.</p>
  
  <p>Here is what I came up with based off the example from Oracle:</p>
  
  <pre><code>SQL&gt; CREATE OR REPLACE VIEW cust_order AS
    2   SELECT book_customer.customerid, lastname, state, book_order.orderid, orderdate
    3    FROM book_customer
    4      JOIN book_order ON book_order.customerid = book_customer.customerid
    5  ;
  
  View created.
  
  SQL&gt; 
  SQL&gt;
  SQL&gt; CREATE OR REPLACE TRIGGER cust_order_insert
    2   INSTEAD OF INSERT ON cust_order
    3    REFERENCING NEW AS n
    4     FOR EACH ROW
    5  
    6  DECLARE
    7   rowcnt number;
    8  
    9  BEGIN
   10   SELECT COUNT(*) INTO rowcnt
   11    FROM book_customer
   12     WHERE book_customer.customerid = :n.book_customer.customerid;
   13      IF rowcnt = 0 THEN
   14       INSERT INTO book_customer(customerid, lastname, state)
   15        VALUES (:n.book_customer.customerid, :n.lastname, :n.state);
   16         ELSE
   17         UPDATE book_customer SET book_customer.customerid = :n.customerid
   18          WHERE book_customer.customerid = :n.customerid;
   19       END IF;
   20       
   21   SELECT COUNT(*) INTO rowcnt
   22    FROM book_order
   23     WHERE book_order.orderid = :n.orderid;
   24      IF rowcnt = 0 THEN
   25       INSERT INTO book_order(orderid, orderdate)
   26        VALUES (:n.book_order.orderid, :n.orderdate);
   27         ELSE
   28         UPDATE book_order SET book_order.orderid = :n.orderid
   29          WHERE book_order.orderdate = :n.orderdate;
   30       END IF;
   31  END;
   32  
   33  
   34  
   35  
   36  --SQL&gt; prompt won't come back.
   37  
   38  
   39  
   40  
  </code></pre>
  
  <p>Here are the table descriptions:</p>
  
  <pre><code>SQL&gt; desc cust_order;
   Name                                                              Null?    Type
   ----------------------------------------------------------------- -------- ------------------------
   CUSTOMERID                                                        NOT NULL NUMBER(4)
   LASTNAME                                                                   VARCHAR2(10)
   STATE                                                                      VARCHAR2(2)
   ORDERID                                                           NOT NULL NUMBER(4)
   ORDERDATE                                                                  DATE
  </code></pre>
  
  <p>Definitions:</p>
  
  <pre><code>Create table Book_customer
  (CustomerID  NUMBER(4) CONSTRAINT PK_BOOKCUSTOMER PRIMARY KEY,
  LastName VARCHAR2(10),
  FirstName VARCHAR2(10),
  Address VARCHAR2(20),
  City VARCHAR2(20),
  State VARCHAR2(2),
  Zip VARCHAR2(5),
  Referred NUMBER(4));
  
  Create Table Book_order
  (OrderID NUMBER(4) CONSTRAINT PK_BOOKORDER_ORDERID PRIMARY KEY,
  CustomerID NUMBER(4) CONSTRAINT FK_BookOrder_BookCustomer REFERENCES Book_Customer (CustomerID),
  OrderDate DATE,
  ShipDate DATE,
  ShipStreet VARCHAR2(20),
  ShipCity VARCHAR2(20),
  ShipState VARCHAR2(2),
  ShipZip VARCHAR2(5));
  </code></pre>
  
  <p>Any ideas or constructive criticism is appreciated. </p>