用于视图的SQL触发器 [英] SQL Trigger for View

问题描述

在创建视图时需要帮助，然后触发以下操作:输入每个发票行项目时，更新销售员佣金(占销售额的10％)，库存数量和客户余额.

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 item is entered.

这是我现在要查看的内容: 我可以使用它，但是它显示出我在第8行中有错误:

This is what I have right now for the view: I got it working but it shows me that i have error in line 8:

    CREATE TABLE CUSTOMER
(CUSTOMER_ID DECIMAL(2,0) PRIMARY KEY,    
CUSTOMER_NAME CHAR(25),
CUSTOMER_ADDRESS CHAR(15),
CUSTOMER_ZIPCODE DECIMAL(5,0),
CUSTOMER_CITY CHAR(15),
CUSTOMER_STATE CHAR(2),
CUSTOMER_BALANCE DECIMAL(4,2)
);
CREATE TABLE SALESPERSON
(SALESPERSON_ID DECIMAL(3,0) PRIMARY KEY,
SALESPERSON_NAME CHAR(25),
COMMISSION DECIMAL(5,2)
);
CREATE TABLE INVOICE
(INVOICE_ID DECIMAL(3,0),
CUSTOMER_ID DECIMAL(2,0),
SALESPERSON_ID DECIMAL(3,0),
ITEM_NUM DECIMAL(4,0),
INVOICE_DATE DATE,
PRIMARY KEY (CUSTOMER_ID, SALESPERSON_ID),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER,
FOREIGN KEY (SALESPERSON_ID) REFERENCES SALESPERSON
);
CREATE TABLE INVENTORY 
(INV_NUM DECIMAL(4,0) PRIMARY KEY,
DESCRIPTION CHAR(10),
INV_QUANTITY DECIMAL(4,0),
INV_PRICE DECIMAL(7,2),
INV_COST DECIMAL(7,2),
INVOICE_ID DECIMAL(3,0),
ITEM_NUM DECIMAL(4,0),
FOREIGN KEY (INVOICE_ID, ITEM_NUM) REFERENCES INVOICE
);
CREATE TABLE INVOICE_LINE_ITEM
(QUANTITY_SOLD DECIMAL(4,0),
SALE_PRICE DECIMAL(7,2),
INVOICE_ID DECIMAL(3,0),
INV_NUM DECIMAL(4,0),
ITEM_NUM DECIMAL(4,0),
PRIMARY KEY (INVOICE_ID, INV_NUM, ITEM_NUM),
FOREIGN KEY (INVOICE_ID, ITEM_NUM) REFERENCES INVOICE,
FOREIGN KEY (INV_NUM) REFERENCES INVENTORY
);
CREATE TABLE VENDOR
(VENDOR_ID DECIMAL(2,0) PRIMARY KEY,
VENDOR_NAME CHAR(25),
CITY CHAR(15),
STATE CHAR(2),
VENDOR_BALANCE DECIMAL(4,2)
);
CREATE TABLE PURCHASE_ORDER
(PURCHASE_ORDER_ID DECIMAL(2,0) PRIMARY KEY,
BALANCE DECIMAL(4,2),
SHIPMENT CHAR(10),
PURCHASE_ORDER_DATE DATE,
VENDER_ID DECIMAL (2,0),
FOREIGN KEY (VENDER_ID) REFERENCES VENDOR
);
CREATE TABLE PO_LINE_ITEM
(PO_DATE DATE,
PO_BALANCE DECIMAL(4,0),
ITEM_NUM DECIMAL(4,0),
INV_QUANTITY DECIMAL(4,0),
INV_NUM DECIMAL(4,0),
PURCHASE_ORDER_ID DECIMAL(2,0),
PRIMARY KEY (INV_NUM, PURCHASE_ORDER_ID),
FOREIGN KEY (INV_NUM) REFERENCES INVENTORY,
FOREIGN KEY (PURCHASE_ORDER_ID) REFERENCES PURCHASE_ORDER);

SQL> CREATE OR REPLACE VIEW VW_INVOICES
  2  AS
  3  SELECT INVOICE_LINE_ITEM.INVOICE_ID, INVOICE.SALESPERSON_ID,
  4   SALESPERSON.SALESPERSON_NAME, SALESPERSON.COMMISSION,
  5   INVENTORY.ITEM_NUM, INVENTORY.INV_QUANTITY,
  6   INVOICE.CUSTOMER_ID, CUSTOMER.CUSTOMER_BALANCE
  7  FROM INVOICE_LINE_ITEM, INVOICE, SALESPERSON, INVENTORY, CUSTOMER
  8   JOIN INVOICE ON INVOICE.INVOICE_ID = INVOICE_LINE_ITEM.INVOICE_ID
  9   JOIN SALESPERSON ON SALESPERSON.SALESPERSON_ID = INVOICE.SALESPERSON_ID
 10   JOIN INVOICE ON INVOICE.ITEM_NUM = INVENTORY.ITEM_NUM
 11   JOIN CUSTOMER ON CUSTOMER.CUSTOMER_ID = INVOICE.CUSTOMER_ID;
 JOIN INVOICE ON INVOICE.INVOICE_ID = INVOICE_LINE_ITEM.INVOICE_ID
                                      *
ERROR at line 8:
ORA-00904: "INVOICE_LINE_ITEM"."INVOICE_ID": invalid identifier

推荐答案

您已在具有comma的表之间拆分了JOIN语法，这导致了此错误，因此您应该修改sql. 试试这个，可能行得通；)

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
AS
SELECT 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;

关于oracle JOIN，请参阅此联接，或者您可以只使用where子句而不是JOIN

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

这篇关于用于视图的SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！