Sql Trigger For View

I need help with creating a view and then triggers for the following: update salesperson commission(10% of sale), inventory quantity, and customer balance when each invoice line it

Solution 1:

You have splited JOIN syntax between tables with comma which caused this error, so you should modify your sql. Try this, may work;)

CREATE OR REPLACE VIEW VW_INVOICES
ASSELECT INVOICE_LINE_ITEM.INVOICE_ID, INVOICE.SALESPERSON_ID,
  SALESPERSON.SALESPERSON_NAME, SALESPERSON.COMMISSION,
  INVENTORY.ITEM_NUM, INVENTORY.INV_QUANTITY,
  INVOICE.CUSTOMER_ID, CUSTOMER.CUSTOMER_BALANCE
FROM INVOICE_LINE_ITEM
JOIN INVOICE ON INVOICE.INVOICE_ID = INVOICE_LINE_ITEM.INVOICE_ID
JOIN SALESPERSON ON SALESPERSON.SALESPERSON_ID = INVOICE.SALESPERSON_ID
JOIN INVENTORY ON INVOICE.ITEM_NUM = INVENTORY.ITEM_NUM
JOIN CUSTOMER ON CUSTOMER.CUSTOMER_ID = INVOICE.CUSTOMER_ID;

About oracle JOIN, please see this Joins, or you could just use where clause instead of JOIN

Solution 2:

I think it should be INVOICE_LINE_ITEM.INVOICE_ID in the query

 CREATE OR REPLACE VIEW VW_INVOICES
 ASSELECT INVOICE_LINE_ITEM.INVOICE_ID, INVOICE.SALESPERSON_ID,
 SALESPERSON.SALESPERSON_NAME, SALESPERSON.COMMISSION,
 INVENTORY.ITEM_NUM, INVENTORY.INV_QUANTITY,
 INVOICE.CUSTOMER_ID, CUSTOMER.CUSTOMER_BALANCE
 FROM INVOICE_LINE_ITEM, INVOICE, SALESPERSON, INVENTORY, CUSTOMER
 JOIN INVOICE ON INVOICE.INVOICE_ID = INVOICE_LINE_ITEM.INVOICE_ID
 JOIN SALESPERSON ON SALESPERSON.SALESPERSON_ID = INVOICE.SALESPERSON_ID
 JOIN INVOICE ON INVOICE.ITEM_NUM = INVENTORY.ITEM_NUM
 JOIN CUSTOMER ON CUSTOMER.CUSTOMER_ID = INVOICE.CUSTOMER_ID;
 JOIN INVOICE ON INVOICE.INVOICE_ID = INVOICE_LINE_ITEM.INVOICE_ID