Update record across multiple tables with a calculation (total invoice) using an Oracle SQL Script

Question

I have a SERVICE table that stores amounts for services in one table then links to a LINE table which connects to an INVOICE.

I want to create a trigger that updates the total per line based on the amount in the SERVICE table and finally a running total for the invoice in the INVOICE table.

I'm stuck on the UPDATE command to get the 20.00 to update into the LINE table and SERVICE table.

How can I create this 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

As a general rule, I would be a bit uncomfortable with computed columns in a table such as Line or Invoice. Often data integrity issues start creeping in where the the computed result does not equal the stored result.

Rather than having the LINE.LinePrice column, you could have a query that will compute the LinePrice on demand:

    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

Similar with the INVOICE.InvoiceTotal you could do a query something like this:

    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

If you go this route, then you also have to think about what happens when the service price changes. If you print old invoices, are you going to be computing new totals or did you want to use the historical values to compute what the invoice was in the past? Probably the latter, so changes in the Service table would need to be effective dated having the date as part of the key. Then you can keep track of historical prices.

But I also understand wanting a fixed, static value if the invoice is done (usually an archive document). If you want to go the trigger route, the trigger may look something like this (I don't have an Oracle instance available, so you will need to verify syntax):

    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;

This is just trying to do the LinePrice on Insert. You would need to increase the InvoiceTotal as well. And you would need to look after the UPDATE (subtract :old sum, and add :new sum) and DELETE scenarios (subtract :old sum).