oracle发票验证,通过使用Oracle SQL脚本进行计算（总发票）的多个表的更新记录...

我有一个SERVICE表，该表存储一个表中的服务数量，然后链接到连接到INVOICE的LINE表。通过使用Oracle SQL脚本进行计算(总发票)的多个表的更新记录

我想要创建一个触发器，根据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');

2011-11-23

JKK