使用 Oracle SQL 脚本通过计算（总发票）更新多个表中的记录

Question

我有一个 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');

Answer 1

一般来说，我会对行或发票等表中的计算列感到有点不舒服。通常，当计算结果不等于存储结果时，数据完整性问题就会开始蔓延。

您可以使用一个查询来按需计算 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。并且您需要关注更新（减去：旧总和，并添加：新总和）和删除场景（减去：旧总和）。