触发器语句

create --新建触发器
or --或者
replace --替换触发器
trigger --触发
tri_out_ta_presc_d_t --触发器名称
after | before --跟新后 | 跟新前
insert | update | delete --增加 |修改 |删除
on hisdb.out_ta_presc_d_t --触发表名称
for each row
执行语句
declare --申明变量

create or replace trigger tri_out_ta_presc_d_t after insert
on hisdb.out_ta_presc_d_t for each row
declare
ll_count number;
ll_count1 number;
ls_sbrlsh varchar2(20);
ls_jmxlh varchar2(100);
begin
select count(1) into ll_count
from med_td_info tinfo
where tinfo.sypbm = :new.sypdm
and tinfo.sypmc=:new.sypmc
and tinfo.sypgg = :new.sypgg
and tinfo.sdlflbm in (‘D’,‘C’);
if ll_count is null then ll_count := 0; end if;
if ll_count > 0 then
select sbrls into ls_sbrlsh from out_ta_presc_m_t where scfh = :new.scfh;
if ls_sbrlsh is null then ls_sbrlsh := ‘NULL’; end if;
select count(1) into ll_count1
from out_ta_book t
where t.sjmxlh is not null and trunc(:new.dcfrq) - trunc( dghsj ) <= 90
and t.sbrlsh = ls_sbrlsh;
if ll_count1 is null then ll_count1 := 0 ;end if;
if ll_count1 <= 0 then
update out_Ta_book t
set t.sjmxlh = to_char(sysdate,‘yyyy’)||to_char( out_jmxlh.nextval,‘fm00000000’ )
where t.smzhm in ( select smzhm from out_ta_presc_m_t tm where tm.scfh = :new.scfh)
and t.sbrlsh = ls_sbrlsh ;
else
select t.sjmxlh into ls_jmxlh
from out_ta_book t
where t.sjmxlh is not null and sbrlsh = ls_sbrlsh and trunc(:new.dcfrq) - trunc( dghsj ) <= 90
and rownum = 1
order by t.dghsj desc;
update out_Ta_book t
set t.sjmxlh = ls_jmxlh
where t.smzhm in ( select smzhm from out_ta_presc_m_t tm where tm.scfh = :new.scfh)
and t.sbrlsh = ls_sbrlsh ;
end if;
end if;
end;