sql之使用 Oracle SQL 脚本通过计算(总发票)更新跨多个表的记录

我有一个 SERVICE 表，它在一个表中存储服务金额，然后链接到一个 LINE 表，该表连接到一个 INVOICE。

我想创建一个触发器，根据 SERVICE 表中的金额更新每行的总计，最后更新 INVOICE 表中发票的运行总计。

我一直在使用 UPDATE 命令来让 20.00 更新到 LINE 表和 SERVICE 表中。

如何创建此UPDATE？

CREATE TABLE SERVICE    
(     
ServiceID       char(6)         NOT NULL,     
Description varchar(50)     NOT NULL,     
Price           decimal(6,2)    NOT NULL,     
CONSTRAINT PK_ServiceID PRIMARY KEY (ServiceID)    
); 
 
CREATE TABLE INVOICE     
(     
InvoiceID       char(6)     NOT NULL,     
InvoiceTotal    LONG,     
CustomerID      char(6)     NOT NULL,     
EmployeeID      char(6)     NOT NULL,     
InvoiceDate date NOT NULL,     
Notes         varchar(200),     
CONSTRAINT PK_Invoice PRIMARY KEY (InvoiceID),     
CONSTRAINT FK_CUSTOMER FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID),     
CONSTRAINT FK_EMPLOYEE FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID)     
); 
 
CREATE TABLE LINE    (     
LineID    char(6) NOT NULL,     
LineQty  int     NOT NULL,     
LinePrice decimal(6,2),     
InvoiceID char(6) NOT NULL,     
ServiceID char(6) NOT NULL,     
CONSTRAINT  PK_LineID PRIMARY KEY (LineID),     
CONSTRAINT  FK_INVOICE  FOREIGN KEY (InvoiceID) REFERENCES INVOICE(InvoiceID),     
CONSTRAINT  FK_SERVICE  FOREIGN KEY (ServiceID) REFERENCES SERVICE(ServiceID)     
); 
 
INSERT INTO SERVICE(ServiceID, Description, Price)     
VALUES('SE0001', 'Press Shirt', 20.00); 
 
INSERT INTO SERVICE(ServiceID, Description, Price)     
VALUES('SE0002', 'Press Slacks', 15.00); 
 
INSERT INTO INVOICE(InvoiceID, CustomerID, EmployeeID, InvoiceDate)     
VALUES('IN0001', 'CU0001', 'EE0001', '01-SEP-2011'); 
 
INSERT INTO LINE(LineID, LineQty, InvoiceID, ServiceID)     
VALUES('LI0001', '2', 'IN0001', 'SE0001'); 

请您参考如下方法:

一般来说，我对表格中的计算列(例如 Line 或 Invoice)有点不适应。通常，数据完整性问题会在计算结果与存储结果不相等的地方开始蔓延。

您可以使用一个查询来按需计算 LinePrice，而不是使用 LINE.LinePrice 列:

    SELECT l.LineID, l.LineQty, l.LineQty * s.Price AS LinePrice, l.InvoiceID, l.ServiceID 
      FROM LINE l, SERVICE s 
      WHERE s.ServiceID = l.ServiceID 

与 INVOICE.InvoiceTotal 类似，您可以执行如下查询:

    SELECT i.InvoiceID, SUM(x.LinePrice) AS InvoiceTotal 
      FROM INVOICE i 
          ,(SELECT l.InvoiceID, l.LineQty * s.Price AS LinePrice 
              FROM LINE l, SERVICE s 
              WHERE s.ServiceID = l.ServiceID) x 
      WHERE i.InvoiceID = x.InvoiceID 
      GROUP BY i.InvoiceID 

如果你走这条路，那么你还必须考虑服务价格变化时会发生什么。如果您打印旧发票，您是要计算新的总计还是要使用历史值来计算过去的发票？可能是后者，因此服务表中的更改需要在日期作为键的一部分的日期生效。然后您可以跟踪历史价格。

但我也明白，如果发票已完成(通常是存档文档)，则需要一个固定的静态值。如果你想走触发器路线，触发器可能看起来像这样(我没有可用的 Oracle 实例，所以你需要验证语法):

    CREATE OR REPLACE TRIGGER line_insert 
    BEFORE INSERT ON line 
    FOR EACH ROW 
    BEGIN 
        SELECT :new.LineQty * Price 
          INTO :new.LinePrice 
          FROM Service 
          WHERE serviceID = :new.ServiceID; 
    END; 

这只是尝试在插入时执行 LinePrice。您还需要增加 InvoiceTotal。并且您需要处理 UPDATE(减去 :old sum，并添加 :new sum)和 DELETE 场景(减去 :old sum)。