文档介绍:【bjtu_h_reg_info_del_new】删除触发器
CREATE OR REPLACE TRIGGER bjtu_h_reg_info_del
AFTER DELETE ON "HFAN"."BJTU_H_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
declare(声明一个变量)
h_pay_type varchar2(3);(字符型) (数字型:number)
begin
select h_patient_type into h_pay_type
from bjtu_h_patient
where h_patient_id=:;
UPDATE bjtu_h_in_stat1
SET h_reg_num = h_reg_num -1
WHERE (in_stat_day = :)
AND(h_patient_type = h_pay_type) and
(h_dep_code = :);
end;
【bjtu_h_reg_info_ins_new】插入触发器
CREATE OR REPLACE TRIGGER bjtu_h_reg_info_ins
AFTER INSERT ON "HFAN"."BJTU_H_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
declare
h_pay_type varchar2(3);
begin
select h_patient_type into h_pay_type
from bjtu_h_patient
where h_patient_id=:;
UPDATE bjtu_h_in_stat1
SET h_reg_num = h_reg_num + 1(药品分装的时候set 字段 = 值,不加新值)
WHERE (in_stat_day = :)
AND (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
(:,h_pay_type,:, 1);
end if;
end;
[bjtu_h_reg_info_upd_new]修改触发器
CREATE OR REPLACE TRIGGER bjtu_h_reg_info_upd
AFTER UPDATE OF "IN_HOS_DATE","H_DEP_CODE" ON
"HFAN"."BJTU_H_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
declare
h_pay_type varchar2(3);
Begin
select h_patient_type into h_pay_type from bjtu_h_patient where h_patient_id=:;
UPDATE bjtu_h_in_stat1
SET h_reg_num = h_reg_num -1
WHERE (in_stat_day = :) and
(h_patient_type = h_pay_type) and
(h_dep_code = :);
select h_patient_type into h_pay_type from bjtu_h_patient where h_patient_id=:;
UPDATE bjtu_h_in_stat1
SET h_reg_num = h_reg_num + 1
WHERE