医学影像信息系统：医学信息系统.docx

【bjtu_h_reg_info_del_new】删除触发器CREATE OR REPLACE TRIGGER bjtu_h_reg_info_delAFTER DELETE ON HFAN.BJTU_H_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWdeclare（声明一个变量）h_pay_type varchar2(3);（字符型） （数字型：number）begin select h_patient_type into h_pay_type from bjtu_h_patient where h_patient_id=:O.h_patient_id; UPDATE bjtu_h_in_stat1 SET h_reg_num = h_reg_num -1 WHERE (in_stat_day = :O.in_hos_date) AND(h_patient_type = h_pay_type) and (h_dep_code = :O.h_dep_code); end;【bjtu_h_reg_info_ins_new】插入触发器CREATE OR REPLACE TRIGGER bjtu_h_reg_info_insAFTER INSERT ON HFAN.BJTU_H_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWdeclareh_pay_type varchar2(3);begin select h_patient_type into h_pay_type from bjtu_h_patient where h_patient_id=:N.h_patient_id; UPDATE bjtu_h_in_stat1 SET h_reg_num = h_reg_num + 1（药品分装的时候set 字段 = 值，不加新值） WHERE (in_stat_day = :N.in_hos_date) AND (h_dep_code = :N.h_dep_code)and (h_patient_type = h_pay_type); if SQL%NOTFOUND then INSERT into bjtu_h_in_stat1 (in_stat_day,h_patient_type, h_dep_code, h_reg_num) VALUES (:N.in_hos_date,h_pay_type,:N.h_dep_code, 1); end if; end;bjtu_h_reg_info_upd_new修改触发器CREATE OR REPLACE TRIGGER bjtu_h_reg_info_updAFTER UPDATE OF IN_HOS_DATE,H_DEP_CODE ON HFAN.BJTU_H_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWdeclareh_pay_type varchar2(3);Beginselect h_patient_type into h_pay_type from bjtu_h_patient where h_patient_id=:O.h_patient_id; UPDATE bjtu_h_in_stat1 SET h_reg_num = h_reg_num -1 WHERE (in_stat_day = :O.in_hos_date) and (h_patient_type = h_pay_type) and (h_dep_code = :O.h_dep_code);select h_patient_type into h_pay_type from bjtu_h_patient where h_patient_id=:N.h_patient_id; UPDATE bjtu_h_in_stat1 SET h_reg_num = h_reg_num + 1 WHERE (in_stat_day = :N.in_hos_date) AND (h_dep_code = :N.h_dep_code)and (h_patient_type = h_pay_type); if SQL%NOTFOUND then INSERT into bjtu_h_in_stat1 (in_stat_day,h_patient_type, h_dep_code, h_reg_num) VALUES (:N.in_hos_date,h_pay_type,:N.h_dep_code, 1); end if;End ;【bjtu_c_fee_detail_ins】CREATE OR REPLACE TRIGGER bjtu_c_fee_detail_ins AFTER INSERT ON HFAN.BJTU_C_FEE_DETAIL REFERENCING OLD AS O NEW AS N FOR EACH ROWbegin begin UPDATE bjtu_c_patient_acc_fee SET fee_num = fee_num + :N.fee_num WHERE (bjtu_c_patient_acc_fee.c_patient_id =:N.c_patient_id) AND (bjtu_c_patient_acc_fee.c_patient_seq_id =:N.c_patient_seq_id) AND (bjtu_c_patient_acc_fee.c_reg_date = :N.c_reg_date) AND (bjtu_c_patient_acc_fee.account_type = :N.account_type); if SQL%NOTFOUND then INSERT into bjtu_c_patient_acc_fee (c_patient_id, c_patient_seq_id, c_reg_date, account_type, fee_num) VALUES (:N.c_patient_id, :N.c_patient_seq_id, :N.c_reg_date, :N.account_type, :N.fee_num); end if; end;end;【bjtu_c_fee_detail_del】CREATE OR REPLACE TRIGGER bjtu_c_fee_detail_del AFTER DELETE ON HFAN.BJTU_C_FEE_DETAIL REFERENCING OLD AS O NEW AS N FOR EACH ROWbegin begin UPDATE bjtu_c_patient_acc_fee SET fee_num = fee_num - :O.fee_num WHERE (bjtu_c_patient_acc_fee.c_patient_id = :O.c_patient_id) AND (bjtu_c_patient_acc_fee.c_patient_seq_id = :O.c_patient_seq_id) AND (bjtu_c_patient_acc_fee.c_reg_date = :O.c_reg_date) AND (bjtu_c_patient_acc_fee.account_type = :O.account_type); end;end;【bjtu_c_fee_detail_upd】CREATE OR REPLACE TRIGGER bjtu_c_fee_detail_upd AFTER UPDATE OF ACCOUNT_TYPE, FEE_NUM ON HFAN.BJTU_C_FEE_DETAIL REFERENCING OLD AS O NEW AS N FOR EACH ROWbegin begin UPDATE bjtu_c_patient_acc_fee SET fee_num = fee_num - :O.fee_num WHERE (bjtu_c_patient_acc_fee.c_patient_id = :O.c_patient_id) AND (bjtu_c_patient_acc_fee.c_patient_seq_id = :O.c_patient_seq_id) AND (bjtu_c_patient_acc_fee.c_reg_date = :O.c_reg_date) AND (bjtu_c_patient_acc_fee.account_type = :O.account_type); end; begin UPDATE bjtu_c_patient_acc_fee SET fee_num = fee_num + :N.fee_num WHERE (bjtu_c_patient_acc_fee.c_patient_id = :N.c_patient_id) AND (bjtu_c_patient_acc_fee.c_patient_seq_id = :N.c_patient_seq_id) AND (bjtu_c_patient_acc_fee.c_reg_date = :N.c_reg_date) AND (bjtu_c_patient_acc_fee.account_type = :N.account_type); if SQL%NOTFOUND then INSERT into bjtu_c_patient_acc_fee (c_patient_id, c_patient_seq_id, c_reg_date, account_type, fee_num) VALUES (:N.c_patient_id, :N.c_patient_seq_id, :N.c_reg_date, :N.account_type, :N.fee_num); end if; end;end;【bjtu_c_patient_del】CREATE OR REPLACE TRIGGER bjtu_c_patient_delAFTER DELETE ON HFAN.BJTU_C_PATIENTREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE bjtu_c_patient_type SET c_patient_type_num = c_patient_type_num - 1 WHERE bjtu_c_patient_type.c_patient_type = :O.c_patient_type;end;【bjtu_c_patient_ins】CREATE OR REPLACE TRIGGER bjtu_c_patient_insAFTER INSERT ON HFAN.BJTU_C_PATIENTREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE bjtu_c_patient_type SET c_patient_type_num = c_patient_type_num+1 WHERE bjtu_c_patient_type.c_patient_type = :N.c_patient_type; end;【bjtu_c_patient_upd】CREATE OR REPLACE TRIGGER bjtu_c_patient_updAFTER UPDATE OF C_PATIENT_TYPE ON HFAN.BJTU_C_PATIENTREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE bjtu_c_patient_type SET c_patient_type_num = c_patient_type_num - 1 WHERE bjtu_c_patient_type.c_patient_type = :O.c_patient_type; UPDATE bjtu_c_patient_type SET c_patient_type_num = c_patient_type_num+1 WHERE bjtu_c_patient_type.c_patient_type = :N.c_patient_type; end;【bjtu_c_reg_info_del】CREATE OR REPLACE TRIGGER bjtu_c_reg_info_delAFTER DELETE ON HFAN.BJTU_C_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin begin UPDATE bjtu_c_reg_dep_stat SET c_reg_num = c_reg_num - 1 WHERE (bjtu_c_reg_dep_stat.c_stat_day = :O.c_reg_date) AND (bjtu_c_reg_dep_stat.c_reg_type = :O.c_reg_type) AND (bjtu_c_reg_dep_stat.h_dep_code = :O.h_dep_code); end;end;【bjtu_c_reg_info_ins】CREATE OR REPLACE TRIGGER bjtu_c_reg_info_insAFTER INSERT ON HFAN.BJTU_C_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin begin UPDATE bjtu_c_reg_dep_stat SET c_reg_num = c_reg_num + 1 WHERE (bjtu_c_reg_dep_stat.c_stat_day = :N.c_reg_date) AND (bjtu_c_reg_dep_stat.c_reg_type = :N.c_reg_type) AND (bjtu_c_reg_dep_stat.h_dep_code = :N.h_dep_code); if SQL%NOTFOUND then INSERT into bjtu_c_reg_dep_stat (c_stat_day, c_reg_type, h_dep_code, c_reg_num) VALUES (:N.c_reg_date, :N.c_reg_type, :N.h_dep_code, 1); end if; end;end;【bjtu_c_reg_info_upd】CREATE OR REPLACE TRIGGER bjtu_c_reg_info_updAFTER UPDATE OF C_REG_TYPE,H_DEP_CODE ON HFAN.BJTU_C_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE bjtu_c_reg_dep_stat SET c_reg_num = c_reg_num - 1 WHERE (bjtu_c_reg_dep_stat.c_stat_day = :O.c_reg_date) AND (bjtu_c_reg_dep_stat.c_reg_type = :O.c_reg_type) AND (bjtu_c_reg_dep_stat.h_dep_code = :O.h_dep_code); begin UPDATE bjtu_c_reg_dep_stat SET c_reg_num = c_reg_num + 1 WHERE (bjtu_c_reg_dep_stat.c_stat_day = :N.c_reg_date) AND (bjtu_c_reg_dep_stat.c_reg_type = :N.c_reg_type) AND (bjtu_c_reg_dep_stat.h_dep_code = :N.h_dep_code); if SQL%NOTFOUND then INSERT into bjtu_c_reg_dep_stat (c_stat_day, c_reg_type, h_dep_code, c_reg_num) VALUES (:N.c_reg_date, :N.c_reg_type, :N.h_dep_code, 1); end if; end;end;【bjtu_h_reg_info_del】CREATE OR REPLACE TRIGGER bjtu_h_reg_info_delAFTER DELETE ON HFAN.BJTU_H_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE bjtu_h_in_stat SET h_reg_num = h_reg_num -1 WHERE (in_stat_day = :O.in_hos_date) AND (h_dep_code = :O.h_dep_code); end;【bjtu_h_reg_info_ins】CREATE OR REPLACE TRIGGER bjtu_h_reg_info_insAFTER INSERT ON HFAN.BJTU_H_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin begin UPDATE bjtu_h_in_stat SET h_reg_num = h_reg_num + 1 WHERE (in_stat_day = :N.in_hos_date) AND (h_dep_code = :N.h_dep_code); if SQL%NOTFOUND then INSERT into bjtu_h_in_stat (in_stat_day, h_dep_code, h_reg_num) VALUES (:N.in_hos_date, :N.h_dep_code, 1); end if; end;end;【bjtu_h_reg_info_upd】CREATE OR REPLACE TRIGGER bjtu_h_reg_info_updAFTER UPDATE OF IN_HOS_DATE,H_DEP_CODE ON HFAN.BJTU_H_REG_INFOREFERENCING OLD AS O NEW AS NFOR EACH ROWBeginUPDATE bjtu_h_in_stat SET h_reg_num = h_reg_num -1 WHERE (in_stat_day = :O.in_hos_date) AND (h_dep_code = :O.h_dep_code); begin UPDATE bjtu_h_in_stat SET h_reg_num = h_reg_num + 1 WHERE (in_stat_day = :N.in_hos_date) AND (h_dep_code = :N.h_dep_code); if SQL%NOTFOUND then INSERT into bjtu_h_in_stat (in_stat_day, h_dep_code, h_reg_num) VALUES (:N.in_hos_date, :N.h_dep_code, 1); end if; end;End ;插入触发器0CREATE OR REPLACE TRIGGER ic_id_def_insAFTER INSERT ON HFAN.IC_ID_DEFREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE ic_pay_type SET p_num = p_num+1 WHERE ic_pay_type.pay_type = :N.pay_type; end;插入触发器1CREATE OR REPLACE TRIGGER ic_id_def_insAFTER INSERT ON HFAN.IC_ID_DEFREFERENCING OLD AS O NEW AS NFOR EACH ROWdeclare stat_day varchar2(10); begin select to_char(sysdate, yyyy/mm/dd) into stat_day from dual; UPDATE ic_pay_type SET p_num = p_num+1 WHERE ic_pay_type.pay_type = :N.pay_type; begin UPDATE bjtu_c_reg_dep_stat SET c_reg_num = c_reg_num+1 WHERE ( bjtu_c_reg_dep_stat.c_stat_day = :N.c_reg_date ) AND ( bjtu_c_reg_dep_stat.c_reg_type = :N.c_reg_type ) AND ( bjtu_c_reg_dep_stat.h_dep_code = :N.h_dep_code ) ;if SQL%NOTFOUND then INSERT bjtu_c_reg_dep_stat ( c_stat_day, c_reg_type, h_dep_code, c_reg_num) VALUES ( :N.c_reg_date, :N.c_reg_type, :N.h_dep_code, 1 ) ; end if; end;end;删除触发器0CREATE OR REPLACE TRIGGER ic_id_def_delAFTER DELETE ON HFAN.IC_ID_DEFREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE ic_pay_type SET p_num = p_num - 1 WHERE ic_pay_type.pay_type = :O.pay_type;end;删除触发器1CREATE OR REPLACE TRIGGER ic_id_def_delAFTER DELETE ON HFAN.IC_ID_DEFREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE ic_pay_type SET p_num = p_num - 1 WHERE ic_pay_type.pay_type = :O.pay_type; UPDATE ic_id_stat SET p_num = p_num - 1 WHERE ( ic_id_stat.st_day = :O.reg_day ) AND ( ic_id_stat.pay_type = :O.pay_type ) ; end;修改触发器0CREATE OR REPLACE TRIGGER ic_id_def_updAFTER UPDATE OF PAY_TYPE ON HFAN.IC_ID_DEFREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE ic_pay_type SET p_num = p_num - 1 WHERE ic_pay_type.pay_type = :O.pay_type; UPDATE ic_pay_type SET p_num = p_num + 1 WHERE ic_pay_type.pay_type = :N.pay_type; end;修改触发器1CREATE OR REPLACE TRIGGER ic_id_def_updAFTER UPDATE OF PAY_TYPE,REG_DAY ON HFAN.IC_ID_DEFREFERENCING OLD AS O NEW AS NFOR EACH ROWbegin UPDATE ic_pay_type SET p_num = p_num + 1 WHERE ic_pay_type.pay_type = :N.pay_type; UPDATE ic_pay_type SET p_num = p_num - 1 WHERE ic_pay_type.pay_type = :O.pay_type; UPDATE ic_id_stat SET p_num = p_num - 1 WHERE ( ic_id_stat.st_day = :O.reg_day ) AND ( ic_id_stat.pay_type = :O.pay_type ) ; UPDATE ic_id_stat SET p_num = p_num + 1 WHERE ( ic_id_stat.st_day = :N.reg_day ) AND ( ic_id_stat.pay_type = :N.pay_type ) ; end;脚本INSERT INTO ic_id_def ( ic_id, p_name, id_def, address, tel_no, pay_type ) VALUES ( 100, 张三, null, null, null, 01 ) ;commit; INSERT INTO ic_id_def ( ic_id, p_name, id_def, address, tel_no, pay_type, reg_day ) VALUES ( 100, 张三, null, null, null, 01, to_char(sysdate, yyyy/mm/dd) ) ;cselect * from bjtu_c_patient; INSERT INTO bjtu_c_patient ( c_patient_id, c_patient_name, c_patient_age, c_patient_address, c_patient_tel, c_patient_type, c_patient_reg_date, c_patient_seq_id ) VALUES ( 0008, 李六, 19, 22222, 133325366333, 02, 2012/12/12, null ) ;select count(c_patient_id) from bjtu_c_patientwhere c_patient_type=01;select c_patient_type_numfrom bjtu_c_patient_typewhere c_patient_type=01;select sum(c_patient_type_num)from bjtu_c_patient_type DELETE FROM bjtu_c_patient WHERE bjtu_c_patient.c_patient_id = 0008 ; UPDATE bjtu_c_patient SET c_patient_type = 02 WHERE bjtu_c_patient.c_patient_id = 0001 ;12