trigger.sql

Delphi6开发的HIS程序

create or replace trigger job_bi
before insert
on job
for each row
declare
begin
  select ralf.job_id.nextval into :new.jobid from dual;
end;
/

create or replace trigger bed_bi
before insert
on bed
for each row
declare
begin
  select ralf.bed_id.nextval into :new.bedid from dual;
end;
/

create or replace trigger bloodtype_bi
before insert
on bloodtype
for each row
declare
begin
  select ralf.bloodtype_id.nextval into :new.bloodtypeid from dual;
end;
/

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

create or replace trigger checkinmethod_bi
before insert
on checkinmethod
for each row
declare
begin
  select ralf.checkinmethod_id.nextval into :new.checkinmethodid from dual;
end;
/

create or replace trigger checkoutmethod_bi
before insert
on checkoutmethod
for each row
declare
begin
  select ralf.checkoutmethod_id.nextval into :new.checkoutmethodid from dual;
end;
/

create or replace trigger country_bi
before insert
on country
for each row
declare
begin
  select ralf.country_id.nextval into :new.countryid from dual;
end;
/

create or replace trigger damaterial_bi
before insert
on damaterial
for each row
declare
begin
  select ralf.damaterial_id.nextval into :new.damaterialid from dual;
end;
/

create or replace trigger damedicine_bi
before insert
on damedicine
for each row
declare
begin
  select ralf.damedicine_id.nextval into :new.damedicineid from dual;
end;
/

create or replace trigger damedicine_biu
before insert or update
on damedicine
for each row
declare
begin
  if :new.isactive = 1 then
    :new.stopdate := null;
    :new.stopby := null;
  end if;
end;
/

create or replace trigger daoperation_bi
before insert
on daoperation
for each row
declare
begin
  select ralf.daoperation_id.nextval into :new.daoperationid from dual;
end;
/

create or replace trigger daservice_bi
before insert
on daservice
for each row
declare
begin
  select ralf.daservice_id.nextval into :new.daserviceid from dual;
end;
/

create or replace trigger daservice_biu
before insert or update
on daservice
for each row
declare
begin
  if :new.isactive = 1 then
    :new.stopdate := null;
  end if;
end;
/

create or replace trigger datemplate_bi
before insert
on datemplate
for each row
declare
begin
  select ralf.datemplate_id.nextval into :new.datemplateid from dual;
end;
/

create or replace trigger datmaterial_bi
before insert
on datmaterial
for each row
declare
begin
  select ralf.datmaterial_id.nextval into :new.datmaterialid from dual;
end;
/

create or replace trigger datmedicine_bi
before insert
on datmedicine
for each row
declare
begin
  select ralf.datmedicine_id.nextval into :new.datmedicineid from dual;
end;
/

create or replace trigger datoperation_bi
before insert
on datoperation
for each row
declare
begin
  select ralf.datoperation_id.nextval into :new.datoperationid from dual;
end;
/

create or replace trigger datservice_bi
before insert
on datservice
for each row
declare
begin
  select ralf.datservice_id.nextval into :new.datserviceid from dual;
end;
/

create or replace trigger department_bi
before insert
on department
for each row
declare
begin
  select ralf.department_id.nextval into :new.departmentid from dual;
end;
/

create or replace trigger diagnose_bi
before insert
on diagnose
for each row
declare
begin
  select ralf.diagnose_id.nextval into :new.diagnoseid from dual;
end;
/

create or replace trigger doctor_bi
before insert
on doctor
for each row
declare
begin
  select ralf.doctor_id.nextval into :new.doctorid from dual;
end;
/

create or replace trigger doctoradvice_bi
before insert
on doctoradvice
for each row
declare
begin
  select ralf.doctoradvice_id.nextval into :new.doctoradviceid from dual;
end;
/

create or replace trigger dose_bi
before insert
on dose
for each row
declare
begin
  select ralf.dose_id.nextval into :new.doseid from dual;
end;
/

create or replace trigger employee_bi
before insert
on employee
for each row
declare
begin
  select ralf.employee_id.nextval into :new.employeeid from dual;
end;
/

create or replace trigger employee_biu
before insert or update
on employee
for each row
declare
begin
  :new.password := trim(upper(:new.password));
  :new.loginname := trim(upper(:new.loginname));
end;
/

create or replace trigger food_bi
before insert
on food
for each row
declare
begin
  select ralf.food_id.nextval into :new.foodid from dual;
end;
/

create or replace trigger inpatient_bi
before insert
on inpatient
for each row
declare
begin
  select ralf.inpatient_id.nextval into :new.inpatientid from dual;
end;
/

create or replace trigger materialstock_bi
before insert
on materialstock
for each row
declare
begin
  select ralf.materialstock_id.nextval into :new.materialstockid from dual;
end;
/

create or replace trigger materialstockdetail_bi
before insert
on materialstockdetail
for each row
declare
begin
  select ralf.materialstockdetail_id.nextval into :new.materialstockdetailid from dual;
end;
/

create or replace trigger medicinestockdetail_bi
before insert
on medicinestockdetail
for each row
declare
begin
  select ralf.medicinestockdetail_id.nextval into :new.medicinestockdetailid from dual;
end;
/

create or replace trigger medicinestockdetail_biu
before insert or update
on medicinestockdetail
for each row
declare
begin
  if inserting then
    update medicinestock
    set    amount = amount + :new.amount
    where  medicinestock.medicinestockid = :new.medicinestockid;
  end if;
  if updating then
    update medicinestock
    set    amount = amount + (:new.amount - :old.amount)
    where  medicinestock.medicinestockid = :new.medicinestockid;
  end if;
end;
/

create or replace trigger medicinestockdetail_bd
before delete
on medicinestockdetail
for each row
declare
begin
  update medicinestock
  set    amount = amount - :old.amount
  where  medicinestock.medicinestockid = :new.medicinestockid;
end;
/

create or replace trigger nation_bi
before insert
on nation
for each row
declare
begin
  select ralf.nation_id.nextval into :new.nationid from dual;
end;
/

create or replace trigger nativeplace_bi
before insert
on nativeplace
for each row
declare
begin
  select ralf.nativeplace_id.nextval into :new.nativeplaceid from dual;
end;
/

create or replace trigger paymentmethod_bi
before insert
on paymentmethod
for each row
declare
begin
  select ralf.paymentmethod_id.nextval into :new.paymentmethodid from dual;
end;
/

create or replace trigger pcomaterial_bi
before insert
on pcomaterial
for each row
declare
begin
  select ralf.pcomaterial_id.nextval into :new.pcomaterialid from dual;
end;
/

create or replace trigger pcomedicine_bi
before insert
on pcomedicine
for each row
declare
begin
  select ralf.pcomedicine_id.nextval into :new.pcomedicineid from dual;
end;
/

create or replace trigger pcomedicine_bu
before update
on pcomedicine
for each row
declare
  l_siomedicinedetailid integer;
begin
  if :new.isapprove = 1 and :old.isapprove = 0 then
    select siomedicinedetailid into l_siomedicinedetailid from medicinestockdetail where medicinestockdetailid = :new.medicinestockdetailid;
    update siomedicinedetail set sprice = :new.newprice where siomedicinedetailid = l_siomedicinedetailid and sprice = :new.oldprice;
  end if;
end;
/

create or replace trigger personrelation_bi
before insert
on personrelation
for each row
declare
begin
  select ralf.personrelation_id.nextval into :new.personrelationid from dual;
end;
/

create or replace trigger pomaterialdetail_bi
before insert
on pomaterialdetail
for each row
declare
begin
  select ralf.pomaterialdetail_id.nextval into :new.pomaterialdetailid from dual;
end;
/

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

create or replace trigger pomedicinedetail_bi
before insert
on pomedicinedetail
for each row
declare
begin
  select ralf.pomedicinedetail_id.nextval into :new.pomedicinedetailid from dual;
end;
/

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

create or replace trigger profession_bi
before insert
on profession
for each row
declare
begin
  select ralf.profession_id.nextval into :new.professionid from dual;
end;
/

create or replace trigger pscomedicine_bi
before insert
on pscomedicine
for each row
declare
begin
  select ralf.pscomedicine_id.nextval into :new.pscomedicineid from dual;
end;
/

create or replace trigger pscomedicine_bu
before update
on pscomedicine
for each row
declare
  l_amount number(16, 4);
begin
  if :new.isapprove = 1 and :old.isapprove = 0 then
    update pstockmedicine set amount = amount + :new.cquantity where pstockmedicineid = :new.pstockmedicineid and amount = :new.oamount;
  end if;
end;
/

create or replace trigger psiodetail_bi
before insert
on psiodetail
for each row
declare
begin
  select ralf.psiodetail_id.nextval into :new.psiodetailid from dual;
end;
/

create or replace trigger psoodetail_bi
before insert
on psoodetail
for each row
declare
begin
  select ralf.psoodetail_id.nextval into :new.psoodetailid from dual;
end;
/

create or replace trigger pstockinorder_bi
before insert
on pstockinorder
for each row