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

【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=: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_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=: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_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=: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 ROW
begin
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 ROW
begin
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 ROW
begin
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_del
AFTER DELETE ON "HFAN"."BJTU_C_PATIENT"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin

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_ins
AFTER INSERT ON "HFAN"."BJTU_C_PATIENT"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin

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_upd
AFTER UPDATE OF "C_PATIENT_TYPE" ON "HFAN"."BJTU_C_PATIENT"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin

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_del
AFTER DELETE ON "HFAN"."BJTU_C_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin
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_ins
AFTER INSERT ON "HFAN"."BJTU_C_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin
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_upd
AFTER UPDATE OF "C_REG_TYPE","H_DEP_CODE" ON
"HFAN"."BJTU_C_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
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);
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_del
AFTER DELETE ON "HFAN"."BJTU_H_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin
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_ins
AFTER INSERT ON "HFAN"."BJTU_H_REG_INFO"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin
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_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
Begin
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);
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 ;
插入触发器0
CREATE OR REPLACE TRIGGER ic_id_def_ins
AFTER INSERT ON "HFAN"."IC_ID_DEF"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin

UPDATE ic_pay_type
SET p_num = p_num+1
WHERE ic_pay_type.pay_type = :N.pay_type;

end;
插入触发器1
CREATE OR REPLACE TRIGGER ic_id_def_ins
AFTER INSERT ON "HFAN"."IC_ID_DEF"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
declare
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;
删除触发器0
CREATE OR REPLACE TRIGGER ic_id_def_del
AFTER DELETE ON "HFAN"."IC_ID_DEF"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin
UPDATE ic_pay_type
SET p_num = p_num - 1
WHERE ic_pay_type.pay_type = :O.pay_type;
end;
删除触发器1
CREATE OR REPLACE TRIGGER ic_id_def_del
AFTER DELETE ON "HFAN"."IC_ID_DEF"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin

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;
修改触发器0
CREATE OR REPLACE TRIGGER ic_id_def_upd
AFTER UPDATE OF "PAY_TYPE" ON "HFAN"."IC_ID_DEF"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin

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;
修改触发器1
CREATE OR REPLACE TRIGGER ic_id_def_upd
AFTER UPDATE OF "PAY_TYPE","REG_DAY" ON "HFAN"."IC_ID_DEF"
REFERENCING OLD AS O NEW AS N
FOR EACH ROW
begin

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') ) ;
c
select * 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_patient
where c_patient_type='01';

select c_patient_type_num
from bjtu_c_patient_type
where 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