create or replace trigger tri02
after insert on des_13
for each row
declare
c_des_0 des_13.cardid%type;
c_des_1 des_13.workage%type;
c_des_2 des_13.everym%type;
c_des_3 des_13.worktype%type;
--des_13.worktypeºÍdes_11.cname½øÐйØÁª
c_des_4 des_15.wtcode%type;
--des_15.wtcodeºÍdes_11.coden½øÐйØÁª
c_des_5 des_15.wtsys%type;
c_des_6 des_15.wttr%type;
/*cursor c_des is
select workage,everym
from des_13
where cardid =:new.cardid;*/
begin
/*open c_des;
fetch c_des into c_des_1,c_des_3;*/
select workage
into c_des_1
from des_13
where cardid=:new.cardid;
select worktype
into c_des_3
from des_13
where cardid=:new.cardid;
select coden
into c_des_4
from des_11
where cname=c_des_3;
select wtsys,wttr
into c_des_5,c_des_6
from des_15
where wtcode=c_des_4;
c_des_2:=c_des_1*c_des_5*c_des_6;
update des_13
set worktype=c_des_2
where cardid=c_des_0;
--close c_des;
end tri02;
-------------------------------------------
after insert on des_13
for each row
declare
c_des_0 des_13.cardid%type;
c_des_1 des_13.workage%type;
c_des_2 des_13.everym%type;
c_des_3 des_13.worktype%type;
--des_13.worktypeºÍdes_11.cname½øÐйØÁª
c_des_4 des_15.wtcode%type;
--des_15.wtcodeºÍdes_11.coden½øÐйØÁª
c_des_5 des_15.wtsys%type;
c_des_6 des_15.wttr%type;
/*cursor c_des is
select workage,everym
from des_13
where cardid =:new.cardid;*/
begin
/*open c_des;
fetch c_des into c_des_1,c_des_3;*/
select workage
into c_des_1
from des_13
where cardid=:new.cardid;
select worktype
into c_des_3
from des_13
where cardid=:new.cardid;
select coden
into c_des_4
from des_11
where cname=c_des_3;
select wtsys,wttr
into c_des_5,c_des_6
from des_15
where wtcode=c_des_4;
c_des_2:=c_des_1*c_des_5*c_des_6;
update des_13
set worktype=c_des_2
where cardid=c_des_0;
--close c_des;
end tri02;
-------------------------------------------
after insert on des_13
for each row
declare
c_des_0 des_13.cardid%type;
c_des_1 des_13.workage%type;
c_des_2 des_13.everym%type;
c_des_3 des_13.worktype%type;
c_des_4 des_15.wtcode%type;
c_des_5 des_15.wtsys%type;
c_des_6 des_15.wttr%type;
begin
select workage
into c_des_1
from des_13
where cardid=:new.cardid;
select worktype
into c_des_3
from des_13
where cardid=:new.cardid;
select coden
into c_des_4
from des_11
where cname=c_des_3;
select wtsys,wttr
into c_des_5,c_des_6
from des_15
where wtcode=c_des_4;
c_des_2:=c_des_1*c_des_5*c_des_6;
update des_13
set worktype=c_des_2
where cardid=c_des_0;
end tri02;
-----------------------------------------
刚才那个看起来比较乱
我的设想是这样的:
在一个表中insert一条记录时,取出新进入的记录的其中几个字段的值,根据这几个值,查询其他几个相关表,进行一些计算,然后将新进入的记录的其中一个字段进行更新
现在就是这个样子,晕了
before insert on des_13
for each row
...
begin
取出新进入的记录的其中几个字段的值;
查询其他几个相关表,进行一些计算;
select 新值 into :new.字段 from dual;
其它操作;
end;
select ... into :new.字段名 from dual就是将待插入的新值修改为...的内容
into c_des_1
from des_13
where cardid=:new.cardid;select worktype
into c_des_3
from des_13
where cardid=:new.cardid;
我的理解是,以上是你插入新记录中的相关字段,可以用:new.workage 和 :new.worktype代替,另外,
这条语句:
update des_13
set worktype=c_des_2
where cardid=c_des_0;
没看见你给c_des_0赋值阿,是不是就是:new.cardid 呢
create or replace trigger tri02
before insert on des_13
for each row
declare
c_des_0 des_13.cardid%type;
c_des_1 des_13.workage%type;
c_des_2 des_13.everym%type;
c_des_3 des_13.worktype%type;
c_des_4 des_15.wtcode%type;
c_des_5 des_15.wtsys%type;
c_des_6 des_15.wttr%type;
begin select coden into c_des_4
from des_11
where cname=:new.workage; select wtsys,wttr into c_des_5,c_des_6
from des_15
where wtcode=:new.worktype; c_des_2:=:new.workage*c_des_5*c_des_6;
:new.worktype := c_des_2 ;
exception
when NO_DATA_FOUND THEN
:new.worktype := 0;
when others then
dbms_output.put_line('出错了!');
end tri02;
select nvl(max(coden)) into c_des_4
from des_11
where cname=:new.workage; select nvl(max(wtsys)),nvl(max(wttr)) into c_des_5,c_des_6
from des_15
where wtcode=:new.worktype;