trigger.sql

Delphi6开发的HIS程序

declare
begin
  select ralf.pstockinorder_id.nextval into :new.pstockinorderid from dual;
end;
/

create or replace trigger pstockinorder_biu
  before insert or update on pstockinorder
  for each row
declare
  l_count            integer;
  l_pstockmedicineid integer;
  l_packamount       integer;
  l_medicineid       integer;
  l_price            number(16,
                            4);
  l_amount           number(16,
                            4);
  result integer;                           
begin
  :new.price := sp_medicinestockfindprice(:new.medicinestockid);

  if :new.isapprove = 1 and :old.isapprove = 0 then
    select medicineid
    into   l_medicineid
    from   medicinestock
    where  medicinestockid = :new.medicinestockid;

    select packamount
    into   l_packamount
    from   medicine
    where  medicineid = l_medicineid;
  
    l_amount := :new.amount * l_packamount;
    l_price  := :new.price / l_packamount;
  
    select count(*)
    into   l_count
    from   pstockmedicine
    where  medicinestockid = :new.medicinestockid and price = l_price;
  
    if l_count = 0 then
      --  增加库存条目
      insert into pstockmedicine
        (pstockmedicineid,
         samount,
         amount,
         created,
         updated,
         medicinestockid,
         price,
         total)
      values
        (null,
         0,
         l_amount,
         sysdate,
         sysdate,
         :new.medicinestockid,
         l_price,
         null);
    else
      select pstockmedicineid
      into   l_pstockmedicineid
      from   pstockmedicine
      where  medicinestockid = :new.medicinestockid and price = l_price;
    
      --  增加药房库存
      update pstockmedicine
      set    amount = amount + l_amount
      where  pstockmedicineid = l_pstockmedicineid;
    end if;
    --  减少药库库存
    result := sp_medicinestockdecrease2(:new.medicinestockid, :new.amount);
  end if;
  select sysdate into :new.updated from dual;
end;
/

create or replace trigger pstockmedicine_bi
before insert
on pstockmedicine
for each row
declare
begin
  select ralf.pstockmedicine_id.nextval into :new.pstockmedicineid from dual;
end;
/

create or replace trigger pstockmedicine_biu
before insert or update
on pstockmedicine
for each row
declare
begin
  if :new.price is null then
    :new.price := sp_medicinestockfindminsprice(:new.medicinestockid);
    if :new.price < 0 then
      raise_application_error(-20001, '库房没有库存,不能确定单价');
    end if;
  end if;
  if :new.price < 0 then
    raise_application_error(-20001, '库存不足');
  else
    :new.total := nvl(:new.price, 0) * nvl(:new.amount, 0);
  end if;
end;
/

create or replace trigger purchasemethod_bi
before insert
on purchasemethod
for each row
declare
begin
  select ralf.purchasemethod_id.nextval into :new.purchasemethodid from dual;
end;
/

create or replace trigger purchaseorder_bi
before insert
on purchaseorder
for each row
declare
begin
  select ralf.purchaseorder_id.nextval into :new.purchaseorderid from dual;
end;
/

create or replace trigger recipe_bi
before insert
on recipe
for each row
declare
begin
  select ralf.recipe_id.nextval into :new.recipeid from dual;
  select ralf.reckoning_id.nextval into :new.reckoningid from dual;
  insert into reckoning(reckoningid, created, updated, charge, isactive, isvalid, sickid)
  values(:new.reckoningid, sysdate, sysdate, 0, 1, 1, :new.sickid);
end;
/

create or replace trigger recipe_bu
before update
on recipe
for each row
declare
begin
  if :new.isvalid = 1 and :old.isvalid = 0 then
     update reckoning set isvalid = 1 where reckoningid = :new.reckoningid;
     sp_rmedicinesetactive(:new.reckoningid);
  end if;
end;
/

create or replace trigger reckoning_bi
before insert
on reckoning
for each row
declare
begin
  if :new.reckoningid is null then
    select ralf.reckoning_id.nextval into :new.reckoningid from dual;
  end if;
end;
/

create or replace trigger rmaterial_bi
before insert
on rmaterial
for each row
declare
begin
  select ralf.rmaterial_id.nextval into :new.rmaterialid from dual;
end;
/

create or replace trigger rmedicine_bi
before insert
on rmedicine
for each row
declare
begin
  select ralf.rmedicine_id.nextval into :new.rmedicineid from dual;
end;
/

create or replace trigger rmedicine_biu
before insert or update
on rmedicine
for each row
declare
  l_medicinestockid integer;
  l_pstockoutorderid integer;
begin
  :new.price := sp_medicinestockfindminsprice2(:new.pstockmedicineid);
  :new.total := :new.amount * :new.price;
  
  select medicinestockid into l_medicinestockid from pstockmedicine 
  where pstockmedicineid = :new.pstockmedicineid;

  select medicineid into :new.medicineid from medicinestock where medicinestockid = l_medicinestockid;

  if updating and :new.isactive = 0 and :old.isactive = 1 then
    select pstockoutorder_id.nextval into l_pstockoutorderid from dual;
    insert into pstockoutorder
    (
      pstockoutorderid,
      total,
      taxrate,
      amount,
      isactive,
      isvalid,
      isapprove,
      approvedate,
      created,
      updated,
      checkoutdate,
      pstockmedicineid,
      createby,
      updateby,
      approveby,
      price,
      departmentid
    )
    values
    (
      l_pstockoutorderid,
      0,
      null,
      :new.amount,
      0,
      0,
      0,
      null,
      sysdate,
      sysdate,
      sysdate,
      :new.pstockmedicineid,
      null,
      null,
      null,
      :new.price,
      null
    );
    update pstockoutorder set isapprove = 1, approvedate = sysdate where pstockoutorderid = l_pstockoutorderid;
  end if;
end;
/

create or replace trigger roperation_bi
before insert
on roperation
for each row
declare
begin
  select ralf.roperation_id.nextval into :new.roperationid from dual;
end;
/

create or replace trigger rservice_bi
before insert
on rservice
for each row
declare
begin
  select ralf.rservice_id.nextval into :new.rserviceid from dual;
end;
/

create or replace trigger rservice_biu
before insert or update
on rservice
for each row
declare
begin
  if :new.price is null then
    :new.price := sp_servicefindprice(:new.serviceid);
  end if;
  :new.total := :new.amount * :new.price;
end;
/

create or replace trigger scomaterial_bi
before insert
on scomaterial
for each row
declare
begin
  select ralf.scomaterial_id.nextval into :new.scomaterialid from dual;
end;
/

create or replace trigger scomedicine_bi
before insert
on scomedicine
for each row
declare
begin
  select ralf.scomedicine_id.nextval into :new.scomedicineid from dual;
end;
/

create or replace trigger scomedicine_bu
before insert or update
on scomedicine
for each row
declare
begin
  if :new.isapprove = 1 and :old.isapprove = 0 then
    update medicinestockdetail set amount = amount + :new.cquantity 
    where medicinestockdetailid = :new.medicinestockdetailid and amount = :new.oamount;
  end if;
end;
/

create or replace trigger sectionoffice_bi
before insert
on sectionoffice
for each row
declare
begin
  select ralf.sectionoffice_id.nextval into :new.sectionofficeid from dual;
end;
/

create or replace trigger sick_bi
before insert
on sick
for each row
declare
  l_code      varchar(32);
  l_i_code    integer;
  l_recipe_id integer;
begin
  select paramvalue
  into   l_code
  from   param
  where  paramname = 'beginsickcode';
  l_i_code := to_number(l_code) + 1;
  update param
  set    paramvalue = trim(to_char(l_i_code, '000000'))
  where  paramname = 'beginsickcode';
  :new.code := trim(to_char(sysdate, 'yy')) ||
               trim(to_char(l_i_code, '000000'));
  select sick_id.nextval into :new.sickid from dual;
end;
/

create or replace trigger sicknesslevel_bi
before insert
on sicknesslevel
for each row
declare
begin
  select ralf.sicknesslevel_id.nextval into :new.sicknesslevelid from dual;
end;
/

create or replace trigger sicknessstate_bi
before insert
on sicknessstate
for each row
declare
begin
  select ralf.sicknessstate_id.nextval into :new.sicknessstateid from dual;
end;
/

create or replace trigger sickroom_bi
before insert
on sickroom
for each row
declare
begin
  select ralf.sickroom_id.nextval into :new.sickroomid from dual;
end;
/

create or replace trigger sicktype_bi
before insert
on sicktype
for each row
declare
begin
  select ralf.sicktype_id.nextval into :new.sicktypeid from dual;
end;
/

create or replace trigger siomaterialdetail_bi
before insert
on siomaterialdetail
for each row
declare
begin
  select ralf.siomaterialdetail_id.nextval into :new.siomaterialdetailid from dual;
end;
/

create or replace trigger siomaterialdetail_biu
before insert or update
on siomaterialdetail
for each row
declare
begin
  :new.total := :new.amount * :new.price;
end;
/

create or replace trigger siomedicinedetail_bi
before insert
on siomedicinedetail
for each row
declare
begin
  select ralf.siomedicinedetail_id.nextval into :new.siomedicinedetailid from dual;
end;
/

create or replace trigger siomedicinedetail_biu
before insert or update
on siomedicinedetail
for each row
declare
begin
  if :new.pomedicinedetailid is not null then
    select medicineid
    into   :new.medicineid
    from   pomedicinedetail
    where  pomedicinedetailid = :new.pomedicinedetailid;
  end if;

  :new.total := :new.amount * :new.price;

  if inserting then
    update stockinorder
    set    total = total + :new.amount * :new.price
    where  stockinorderid = :new.stockinorderid;
  end if;
  if updating then
    update stockinorder
    set    total = total - :old.amount * :old.price +
                   :new.amount * :new.price
    where  stockinorderid = :new.stockinorderid;
  end if;
end;
/

create or replace trigger siomedicinedetail_bd
  before delete on siomedicinedetail
  for each row
declare
begin
  update stockinorder
  set    total = total - :old.amount * :old.price
  where  stockinorderid = :new.stockinorderid;
end;
/

create or replace trigger soomaterialdetail_bi
before insert
on soomaterialdetail
for each row
declare
begin
  select ralf.soomaterialdetail_id.nextval into :new.soomaterialdetailid from dual;
end;
/

create or replace trigger soomaterialdetail_biu
before insert or update
on soomaterialdetail
for each row
declare
begin
  :new.total := :new.amount * :new.price;
end;
/

create or replace trigger soomedicinedetail_bi
before insert
on soomedicinedetail
for each row
declare
begin
  select ralf.soomedicinedetail_id.nextval into :new.soomedicinedetailid from dual;
end;
/

create or replace trigger soomedicinedetail_biu
  before insert or update on soomedicinedetail
  for each row
declare
begin
  if :new.amount > sp_medicinestockfindamount(:new.medicinestockid) then
    raise_application_error(-20001,
                            '出库数量不能大于当前批次数量');
  end if;

  :new.price := sp_medicinestockfindprice(:new.medicinestockid);
  :new.total := :new.amount * :new.price;
 
  update stockoutorder
  set    total = total - nvl(:old.amount,
                             0) * nvl(:old.price,
                                              0) + :new.amount * :new.price
  where  stockoutorderid = :new.stockoutorderid;

  select medicineid