写成存储过程或者触发器当添加订单明细中的产品时(insert),自动填写其单价;
当添加、修改或删除订单明细中的“产品数量”时(insert,update,delete),“产品库存量”要随之自动变化;create table Product
(
ProID NUMBER not null,
ProName VARCHAR2(20),
SupID NUMBER,
ClassID NUMBER,
UnitNum VARCHAR2(20),
Price number(7,2),
Stock NUMBER,
Orders NUMBER,
Reorders NUMBER,
Stop VARCHAR2(5) not null
);create table Orders
(
OrdersID number(7) not null,
CusID varchar2(5),
EmpID number,
OrderDate DATE,
ArrivalDate DATE,
DispatchDate DATE,
Deliverier varchar2(20),
Deliveryfee number(7,2),
HostName varchar2(40),
HostAddress varchar2(60),
HostCity varchar2(25),
HostArea varchar2(25),
HostPC varchar2(20),
HostState varchar2(25)
);create table SaleOrderItem
(
OrderID number,
ProductID number,
Price number(7,2),
Num number,
Disconut number(7,2)
);
当添加、修改或删除订单明细中的“产品数量”时(insert,update,delete),“产品库存量”要随之自动变化;create table Product
(
ProID NUMBER not null,
ProName VARCHAR2(20),
SupID NUMBER,
ClassID NUMBER,
UnitNum VARCHAR2(20),
Price number(7,2),
Stock NUMBER,
Orders NUMBER,
Reorders NUMBER,
Stop VARCHAR2(5) not null
);create table Orders
(
OrdersID number(7) not null,
CusID varchar2(5),
EmpID number,
OrderDate DATE,
ArrivalDate DATE,
DispatchDate DATE,
Deliverier varchar2(20),
Deliveryfee number(7,2),
HostName varchar2(40),
HostAddress varchar2(60),
HostCity varchar2(25),
HostArea varchar2(25),
HostPC varchar2(20),
HostState varchar2(25)
);create table SaleOrderItem
(
OrderID number,
ProductID number,
Price number(7,2),
Num number,
Disconut number(7,2)
);
当添加、修改或删除订单明细中的“产品数量”时(insert,update,delete),“产品库存量”要随之自动变化;楼主应该写出是哪张表中的哪个字段需要发生变化,然后将每张表中的各个字段都进行注释~~~
after insert on SaleOrderItem
for each row
declare
v_Price Product.Price%type;
begin
select Price into v_Price from Product
where ProID=:new.ProductID;
dbms_output.put_line('new.ProductID: '||:new.ProductID); insert into SaleOrderItem(Price) values(v_Price);
end;
结果老说这个错误,谁能看看怎么改一下?
SQL> insert into SaleOrderItem(OrderID,ProductId) values(1,1);
new.ProductID: 1
insert into SaleOrderItem(OrderID,ProductId) values(1,1)
*
第 1 行出现错误:
ORA-04091: 表 U1.SALEORDERITEM 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "U1.TRI_INSERTPRICE", line 7
ORA-04088: 触发器 'U1.TRI_INSERTPRICE' 执行过程中出错