create table Stock
(
Icode varchar2(20) primary key not null,
Idescrip varchar2(20),
Pqty varchar2(20)
)
create table Iput
(
IID varchar2(20),
Icode varchar2(20),
Idescrip varchar2(20),
Pqty number(10),
IDate date,
constraint pk_Iput primary key(IID,Icode)
)
触发器
create or replace trigger Iput_tr
before insert on Iput
for each row
declare
i number(10);
b Stock.Icode%type;
cursor c1 is select Icode from Stock;begin
open c1;
fetch c1 into b;
while(c1%found) loop
if(b=:new.Icode)then
dbms_output.put_line('aa11');
select Pqty into i from Iput;
update Stock set Pqty=Pqty+i;
dbms_output.put_line('aa');
else
insert into Stock values(:new.Icode,:new.Pqty,:new.Idescrip);
end if;
fetch c1 into b;
end loop;
close c1;
end;我一向Iput表里插入数据就提示未找到数据!请大虾帮帮忙看看 或重写一个!
(
Icode varchar2(20) primary key not null,
Idescrip varchar2(20),
Pqty varchar2(20)
)
create table Iput
(
IID varchar2(20),
Icode varchar2(20),
Idescrip varchar2(20),
Pqty number(10),
IDate date,
constraint pk_Iput primary key(IID,Icode)
)
触发器
create or replace trigger Iput_tr
before insert on Iput
for each row
declare
i number(10);
b Stock.Icode%type;
cursor c1 is select Icode from Stock;begin
open c1;
fetch c1 into b;
while(c1%found) loop
if(b=:new.Icode)then
dbms_output.put_line('aa11');
select Pqty into i from Iput;
update Stock set Pqty=Pqty+i;
dbms_output.put_line('aa');
else
insert into Stock values(:new.Icode,:new.Pqty,:new.Idescrip);
end if;
fetch c1 into b;
end loop;
close c1;
end;我一向Iput表里插入数据就提示未找到数据!请大虾帮帮忙看看 或重写一个!
before insert on Iput
for each row
declare
i number(10);
begin
selelct count(*) into i from Stock where Icode = :new.Icode;
if i=0 then
insert into Stock(Icode,Pqty,Idescrip)
values(:new.Icode,:new.Pqty,:new.Idescrip);
else
update Stock set Pqty=Pqty+:new.Pqty where Icode = :new.Icode;
end if;
end;
create table Stock
(
Icode varchar2(20) primary key not null,
Idescrip varchar2(20),
Pqty number(20) --注意这个字段应该是NUMBER
);第二个问题:注意我写的触发器的思路是否符合你的需求:
create or replace trigger Iput_tr
after insert on Iput
for each row
declare
i number(10);
begin
select count(*) into i from stock where icode=:new.icode;
if i=0 then
insert into Stock(Icode,Idescrip,Pqty)values(:new.Icode,:new.Idescrip,:new.Pqty);
else
update Stock set Pqty=Pqty+:new.Pqty;
end if;
end;
/注意那里 insert 语句写出了字段列表.测试结果:SQL> insert into iput values('123','123','adfb',10,sysdate);已创建 1 行。SQL> commit;提交完成。SQL> insert into iput values('124','123','adfb',10,sysdate);已创建 1 行。SQL> commit;提交完成。SQL> select * from stock;ICODE IDESCRIP PQTY
-------------------- -------------------- ----------
123 adfb 20SQL> insert into iput values('125','123','adfb',10,sysdate);已创建 1 行。SQL> commit;提交完成。SQL> select * from stock;ICODE IDESCRIP PQTY
-------------------- -------------------- ----------
123 adfb 30