trigger.sql

Delphi6开发的HIS程序

  into   :new.medicineid
  from   medicinestock
  where  medicinestockid = :new.medicinestockid;
end;
/

create or replace trigger soomedicinedetail_bd
  before delete on soomedicinedetail
  for each row
declare
begin
  update stockoutorder
  set    total = total - nvl(:old.amount,
                             0) * nvl(:old.price,
                                              0)
  where  stockoutorderid = :new.stockoutorderid;
end;
/

create or replace trigger stockinorder_bi
before insert
on stockinorder
for each row
declare
begin
  select ralf.stockinorder_id.nextval into :new.stockinorderid from dual;
  :new.isapprove := 0;
end;
/

create or replace trigger stockoutorder_bi
before insert
on stockoutorder
for each row
declare
begin
  if :new.stockoutorderid is null then
    select ralf.stockoutorder_id.nextval into :new.stockoutorderid from dual;
  end if;
  :new.isapprove := 0;
end;
/

create or replace trigger tendlevel_bi
before insert
on tendlevel
for each row
declare
begin
  select ralf.tendlevel_id.nextval into :new.tendlevelid from dual;
end;
/

create or replace trigger tollstation_bi
before insert
on tollstation
for each row
declare
begin
  select ralf.tollstation_id.nextval into :new.tollstationid from dual;
end;
/

create or replace trigger wedlock_bi
before insert
on wedlock
for each row
declare
begin
  select ralf.wedlock_id.nextval into :new.wedlockid from dual;
end;
/

create or replace trigger doctorcatalog_bi
before insert
on doctorcatalog
for each row
declare
begin
  select doctorcatalog_id.nextval into :new.doctorcatalogid from dual;
end;
/

create or replace trigger doctoradvicetype_bi
before insert
on doctoradvicetype
for each row
declare
begin
  select doctoradvicetype_id.nextval into :new.doctoradvicetypeid from dual;
end;
/

create or replace trigger bedcatalog_bi
before insert
on bedcatalog
for each row
declare
begin
  select bedcatalog_id.nextval into :new.bedcatalogid from dual;
end;
/

create or replace trigger times_bi
before insert
on times
for each row
declare
begin
  select times_id.nextval into :new.timesid from dual;
end;
/

create or replace trigger manufacturer_bi
before insert
on manufacturer
for each row
declare
begin
  select manufacturer_id.nextval into :new.manufacturerid from dual;
end;
/

create or replace trigger provider_bi
before insert
on provider
for each row
declare
begin
  select provider_id.nextval into :new.providerid from dual;
end;
/

create or replace trigger area_bi
before insert
on area
for each row
declare
begin
  select area_id.nextval into :new.areaid from dual;
end;
/

create or replace trigger prepaymethod_bi
before insert
on prepaymethod
for each row
declare
begin
  select prepaymethod_id.nextval into :new.prepaymethodid from dual;
end;
/

create or replace trigger frequencyweek_bi
before insert
on frequencyweek
for each row
declare
begin
  select frequencyweek_id.nextval into :new.frequencyweekid from dual;
end;
/

create or replace trigger chargeitem_biu
before insert or update
on chargeitem
for each row
declare
begin
  :new.chargeitemcode := upper(trim(:new.chargeitemcode));
end;
/

create or replace trigger material_bi
before insert
on material
for each row
declare
begin
  select material_id.nextval into :new.materialid from dual;
end;
/

create or replace trigger material_biu
before insert or update
on material
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
end;
/

create or replace trigger materialcatalog_bi
before insert
on materialcatalog
for each row
declare
begin
  select materialcatalog_id.nextval into :new.materialcatalogid from dual;
end;
/

create or replace trigger materialcatalog_biu
before insert or update
on materialcatalog
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
end;
/

create or replace trigger medicine_bi
before insert
on medicine
for each row
declare
begin
  select medicine_id.nextval into :new.medicineid from dual;
end;
/

create or replace trigger medicine_biu
before insert or update
on medicine
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
  :new.caption := upper(trim(:new.caption));
  :new.model := lower(trim(:new.model));
  
  if :new.pinyin is null then
    :new.pinyin := sp_stringtopinyin(:new.caption);
  end if;

  if :new.minunitsid is null then
    :new.minunitsid := :new.unitsid;
  end if;

  if nvl(:new.packamount, 0) < 1 then
    :new.packamount := 1;
  end if;
end;
/

create or replace trigger medicinecatalog_bi
before insert
on medicinecatalog
for each row
declare
begin
  select medicinecatalog_id.nextval into :new.medicinecatalogid from dual;
end;
/

create or replace trigger medicinecatalog_biu
before insert or update
on medicinecatalog
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
  :new.caption := upper(trim(:new.caption));
  if :new.pinyin is null then
    :new.pinyin := sp_stringtopinyin(:new.caption);
  end if;
end;
/

create or replace trigger operation_bi
before insert
on operation
for each row
declare
begin
  select operation_id.nextval into :new.operationid from dual;
end;
/

create or replace trigger operation_biu
before insert or update
on operation
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
  :new.caption := upper(trim(:new.caption));
  if :new.pinyin is null then
    :new.pinyin := sp_stringtopinyin(:new.caption);
  end if;
end;
/

create or replace trigger operationcatalog_bi
before insert
on operationcatalog
for each row
declare
begin
  select operationcatalog_id.nextval into :new.operationcatalogid from dual;
end;
/

create or replace trigger operationcatalog_biu
before insert or update
on operationcatalog
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
  :new.caption := upper(trim(:new.caption));
  if :new.pinyin is null then
    :new.pinyin := sp_stringtopinyin(:new.caption);
  end if;
end;
/

create or replace trigger service_bi
before insert
on service
for each row
declare
begin
  select service_id.nextval into :new.serviceid from dual;
end;
/

create or replace trigger service_biu
before insert or update
on service
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
  :new.caption := upper(trim(:new.caption));
  if :new.pinyin is null then
    :new.pinyin := sp_stringtopinyin(:new.caption);
  end if;
end;
/

create or replace trigger servicecatalog_bi
before insert
on servicecatalog
for each row
declare
begin
  select servicecatalog_id.nextval into :new.servicecatalogid from dual;
end;
/

create or replace trigger servicecatalog_biu
before insert or update
on servicecatalog
for each row
declare
begin
  :new.code := upper(trim(:new.code));
  :new.pinyin := upper(trim(:new.pinyin));
  :new.caption := upper(trim(:new.caption));
  if :new.pinyin is null then
    :new.pinyin := sp_stringtopinyin(:new.caption);
  end if;
end;
/

create or replace trigger approach_bi
before insert
on approach
for each row
declare
begin
  select ralf.approach_id.nextval into :new.approachid from dual;
end;
/

create or replace trigger approach_biu
before insert or update
on approach
for each row
declare
begin
  :new.pinyin := upper(trim(:new.pinyin));
end;
/

create or replace trigger units_bi
before insert
on units
for each row
declare
begin
  select ralf.units_id.nextval into :new.unitsid from dual;
end;
/

create or replace trigger units_biu
before insert or update
on units
for each row
declare
begin
  :new.pinyin := upper(trim(:new.pinyin));
end;
/

create or replace trigger usemethod_bi
before insert
on usemethod
for each row
declare
begin
  select ralf.usemethod_id.nextval into :new.usemethodid from dual;
end;
/

create or replace trigger usemethod_biu
before insert or update
on usemethod
for each row
declare
begin
  :new.pinyin := upper(trim(:new.pinyin));
end;
/

create or replace trigger param_bi
before insert
on param
for each row
declare
begin
  select param_id.nextval into :new.paramid from dual;
end;
/

create or replace trigger pstockoutorder_bi
before insert
on pstockoutorder
for each row
declare
begin
  if :new.pstockoutorderid is null then
    select pstockoutorder_id.nextval into :new.pstockoutorderid from dual;
  end if;
end;
/

create or replace trigger pstockoutorder_biu
before insert or update
on pstockoutorder
for each row
declare
  l_caption medicine.caption%type;
begin
  select price into :new.price from pstockmedicine where :new.pstockmedicineid = pstockmedicineid;
  :new.total := :new.amount * :new.price;
  if :new.isapprove = 1 and :old.isapprove = 0 then
    if sp_pstockcheckout(:new.pstockmedicineid, :new.amount) <> 0 then
      raise_application_error(-20001, '库存不足,库存编号:' || :new.pstockmedicineid);
    end if;
  end if;
end;
/

create or replace trigger medicinestock_biu
before insert or update
on medicinestock
for each row
declare
  l_medicinestockdetailid integer;
  r_msd medicinestockdetail%rowtype;
  l_packamount medicine.packamount%type;
  l_count integer;
begin
  select packamount into l_packamount from medicine where medicineid = :new.medicineid;
  if l_packamount is null then
    :new.minamount := :new.amount;
  else
    :new.minamount := :new.amount * l_packamount;
  end if;
end;
/

create or replace trigger prepayhistory_biu
before insert or update
on prepayhistory
for each row
declare
begin
  update inpatient set prepay = nvl(prepay, 0) - nvl(:old.charge, 0) + nvl(:new.charge, 0) where inpatientid = :new.inpatientid;
  if :new.warrentcode is null then
    :new.warrentcode := trim(to_char(:new.inpatientid, '00000000')) || 
                   trim(to_char(sysdate, 'yyyymmdd'));
  end if;
end;
/