oracle触发器insert6,求教：oracle的insert触发器怎么加入变量

搞定，case用法

create or replace trigger ADDAREA12

before insert on bd_areacl

for each row

declare

-- local variables here

vsupply char(40);

vname char(20);

i number;

begin

for i in 2..12 loop

case

when i=2 then vsupply:='块材类供应商名录';

when i=3 then vsupply:='水泥类供应商名录';

when i=4 then vsupply:='木材类供应商名录';

when i=5 then vsupply:='金属类供应商名录';

when i=6 then vsupply:='高分子材料类供应商名录';

when i=7 then vsupply:='电工材料类供应商名录';

when i=8 then vsupply:='安全防护用品器材供应商名录';

when i=9 then vsupply:='其他材料类供应商名录';

when i=10 then vsupply:='器材租赁供应商名录';

when i=11 then vsupply:='专业分包供应商名录';

else vsupply:='税款、投标类他项名录';

end case;

select areaclname  into vname from bd_areacl where pk_areacl=:new.pk_fatherarea;

if length(:new.areaclcode)='8' and substr(:new.areaclcode,-2,2)='01'

then

insert into bd_areacl

(

areaclcode,

areaclname,

def1,

def2,

def3,

def4,

def5,

dr,

mnecode,

pk_areacl,

pk_corp,

pk_fatherarea,

ts

)

values

(

substr(:new.areaclcode,0,6)||lpad(i,2,'0'),

trim(replace(vname,'市',''))||vsupply ,

:new.def1,

:new.def2,

:new.def3,

:new.def4,

:new.def5,

:new.dr,

:new.mnecode,

lpad(i,2,'0')||substr(:new.pk_areacl,-18,18),

:new.pk_corp,

:new.pk_fatherarea,

:new.ts

);

end if;

end loop;

end ADDAREA12;