簡單的oracle觸發器例子

簡單的oracle觸發器例子

---術前訪視術後隨訪觸發器
create or replace trigger t_update_trigger
after update or insert or delete
on MED_SMARTREPORT_TEMPLATE
for each row
declare
stateCount number;
begin
select count(1) into stateCount from MED_Template_State where ReportTemplateID=:NEW.ReportTemplateID;
if stateCount>0 then
update MED_Template_State set ReportState=ReportState+1 where ReportTemplateID=:NEW.ReportTemplateID;
else
insert into MED_Template_State(ReportTemplateID,ReportState) values(:NEW.ReportTemplateID,1);
end if;
end;

select * from MED_Template

select * from MED_ANESTHESIA_INPUT_DICT for update;
---資料字典觸發器
create or replace trigger trigger_MEDANESTHESIAINPUTDICT
after  insert or update  or delete
on MED_ANESTHESIA_INPUT_DICT
for each row
declare
stateCount number;
begin
select count(1) into stateCount from MED_Template_State where ReportTemplateID=3;
if stateCount>0 then
update MED_Template_State set ReportState=ReportState+1 where ReportTemplateID=3;
else
insert into MED_Template_State(ReportTemplateID,ReportState) values(3,1);
end if;
end;
--select * from MED_Template_State

select * from MED_ANAESTHESIA_DICT for update;

select * from MED_Template_State
---麻醉字典觸發器
create or replace trigger trigger_MEDANAESTHESIADICT
after  insert or update  or delete
on MED_ANAESTHESIA_DICT
for each row
declare
stateCount number;
begin
select count(1) into stateCount from MED_Template_State where ReportTemplateID=4;
if stateCount>0 then
update MED_Template_State set ReportState=ReportState+1 where ReportTemplateID=4;
else
insert into MED_Template_State(ReportTemplateID,ReportState) values(4,1);
end if;
end;