我这几个触发器都不同程度的诡异问题
第一个 最简单 但是就是找不到错
是为了在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
------解决方案--------------------
第一个请参考：
- SQL code
CREATE OR REPLACE TRIGGER InvoicepriceBEFORE INSERT OR UPDATE ON invoiceFOR EACH ROWDECLARE BEGIN IF :NEW.Total < 200 THEN :NEW.Total := 200; END IF;END;