求高手看看啊 几个触发器

Question

我这几个触发器都不同程度的诡异问题第一个 最简单 但是就是找不到错
是为了在Invoice表里只要Total的值小于200就换成200
CREATE OR REPLACE TRIGGER Invoiceprice
AFTER INSERT OR UPDATE ON invoice
DECLARE
Invoiceprice NUMBER(5);
BEGIN
select Total into Invoiceprice
from invoice
IF Invoiceprice<200 THEN 
Total=200;
END IF;
END;
报错是PL/SQL: ORA-00933: SQL command not properly ended第二个
CREATE OR REPLACE TRIGGER driver_order
AFTER INSERT OR UPDATE ON ordertable
DECLARE
cursor c_driver is 
select Drive_license_number,transdate
from ordertable
group by Drive_license_number,transdate;
count_order NUMBER(2);
n_driver ordertable.Drive_license_number%type;
trdate ordertable.transdate%type;
BEGIN
OPEN c_driver;
LOOP 
fetch c_driver into n_driver,trdate;
exit when c_driver%notfound;
select count(order_number)
into count_order
from  ordertable
where  Drive_license_number= n_driver
and transdate= trdate;
IF count_order> 3
THEN RAISE_APPLICATION_ERROR(-20003,
       'A driver can not have more than three orders per day.');
END IF;
END LOOP;
CLOSE c_driver;
END;
报错是PL/SQL: ORA-00904: "TRANSDATE": invalid identifier
而transdate在ordertable里第三个
CREATE OR REPLACE TRIGGER customer_invoice
AFTER INSERT OR UPDATE OF total ON invoice
FOR EACH ROW
DECLARE
cust_price invoice.total%type;
BEGIN
select sum(total)
into cust_price
from  invoice i,ordertable o
where i.invoice_number=o.invoice_number
IF cust_price> 20000
DBMS_OUTPUT.PUT_LINE('The customer has outstanding invoices for more than 20000 euro.');
END IF;
END;报错还是PL/SQL: ORA-00933: SQL command not properly ended

Answer 1

第一个 select Total into Invoiceprice
from invoice；-------------这里少个结束符号

Answer 2

第二个，用pl/sql devloper试一下，看看错误在第一行。没有表结构，看不出来。第三个
where i.invoice_number=o.invoice_number;
少分号

Answer 3

把建表的SQL语句上传一下，可以帮你调试一下。

Answer 4

楼上说的都还是不行
CREATE OR REPLACE TRIGGER Invoiceprice
AFTER INSERT OR UPDATE ON invoice
DECLARE
Total_Price invoice.Total%type;
BEGIN
select Total
into Total_Price
from invoice;
IF Total_Price<200
THEN Total:=200;
END IF;
END;
最后倒数第三行加上冒号还是不行建表的SQL是这个
CREATE TABLE Truck    
(License_number_truck    NUMBER (5),
   Maxload               NUMBER (5),
   Type                   VARCHAR2 (20),
CONSTRAINT PRIM_KEY_Truck    PRIMARY KEY (License_number_truck)
);
CREATE TABLE  Driver(
Drive_license_number   NUMBER(5) ,
driver_name           VARCHAR2 (20),
date_of_birth            DATE,
dateengaged            DATE,
CONSTRAINT PRIM_KEY_Driver    PRIMARY KEY (Drive_license_number)
);
CREATE TABLE Trailer       (
   Trailer_number           NUMBER (5) ,
   Trailer_type              varchar2(20),
CONSTRAINT PRIM_KEY_Trailer    PRIMARY KEY (Trailer_number)
);CREATE TABLE Customer     (
Customer_number     NUMBER (5) ,
Customer_name       VARCHAR2 (20),
Customer_address     VARCHAR2 (20),
Customer_postcode    NUMBER(4),
CONSTRAINT PRIM_KEY_Customer  PRIMARY KEY (Customer_number)
);CREATE TABLE Product     (
Product_number      NUMBER(5),
Product_name         VARCHAR2 (20),   
Product_details       VARCHAR2 (20),
Product_type          VARCHAR2 (20),
CONSTRAINT PRIM_KEY_Product  PRIMARY KEY (Product_number)
);
CREATE TABLE Invoice      (
invoice_number        NUMBER(5),
invoice_date             DATE,
payment_date          DATE,
Place                          VARCHAR2 (20),
Tonnage                  VARCHAR2 (20),
Priceperton                 NUMBER(2),
Total                       NUMBER(5),
CONSTRAINT PRIM_KEY_Invoice    PRIMARY KEY (invoice_number)
);CREATE TABLE Ordertable(
Order_number           NUMBER(10) ,
Product                     Varchar2(20),
Priceperton               NUMBER(10),
transdate                  DATE,
Customer_number       NUMBER (5)       references Customer (Customer_number),
Drive_license_number    NUMBER(5)        references Driver(Drive_license_number),
Trailer_number          NUMBER(5)            references Trailer(Trailer_number),
License_number_truck    NUMBER (5)        references Truck(License_number_truck),
Product_number      NUMBER(5)          references Product(Product_number),
invoice_number        NUMBER(5)       references Invoice(invoice_number),
CONSTRAINT PRIM_KEY_Ordertable   PRIMARY KEY (Order_number));CREATE TABLE Information   (
Order_line                   NUMBER(5)  ,
Information_Date         DATE,
Operations                 VARCHAR2(20),
Place                          VARCHAR2(20),
Bill_number                 NUMBER(10),
Weight                       NUMBER(10),
Order_number         NUMBER(10)        references Ordertable(Order_number),
CONSTRAINT PRIM_KEY_Information  PRIMARY KEY (Order_line)
);
谢谢各位大哥帮忙了~

Answer 5

第一个请参考：CREATE OR REPLACE TRIGGER Invoiceprice
BEFORE INSERT OR UPDATE ON invoice
FOR EACH ROW
DECLARE
    
BEGIN    
    IF :NEW.Total < 200 THEN  
        :NEW.Total := 200;
    END IF;
END;
第二个通过编译，无错误。
第三个请参考：CREATE OR REPLACE TRIGGER customer_invoice
AFTER INSERT OR UPDATE OF total ON invoice
FOR EACH ROW
DECLARE
cust_price invoice.total%type;
BEGIN
select sum(total)
into cust_price
from invoice i,ordertable o where i.invoice_number = o.invoice_number;
IF cust_price > 20000 THEN
    DBMS_OUTPUT.PUT_LINE('The customer has outstanding invoices for more than 20000 euro.');
END IF;
END;