create or replace trigger TRI_HIS_REQUISITION
after update of CHARGE_STATE on HIS_REQUISITION
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROWdeclare
ll_jlxh numeric(18,0);
ll_bqxh numeric(18,0);
ll_fyxm numeric(4,0);
ll_zyh numeric(18,0);
ll_brxz numeric(4,0);
ll_cypb numeric(2,0);
ll_xgpb numeric(1,0);
ld_zfbl numeric(6,2);
ld_zjje numeric(12,2);
ld_fydj numeric(12,2);
ld_zfje numeric(12,2);
ld_fysl numeric(10,2);
ll_zlxz numeric(4,0);
ll_sum1 numeric(2,0);
ll_xmxh numeric(6,0);
ldt_tzsj date;
ldt_qrsj date;
ls_fymc varchar(60);
ls_execute_person varchar(20);
ls_requisition_person varchar(20);
cursor tc_cursor is select jy_ztmx.fyxh from jy_ztmx where jy_ztmx.xmxh = :new.charge_item_id;
--搞清楚his_requisition.his_id是什么
--搞清楚his_requisition.charge_item_id是什么
Begin
--select ll_his_id = his_id ,ldt_requisition_time = requisition_time,ldt_charge_time = charge_time,ll_patient_dept = patient_dept,ls_patient_ward = patient_ward,@execute_dept = execute_dept,@execute_person = execute_person,@requisition_person = requisition_person,@patient_type = patient_type,@charge_state = charge_state from Inserted
--select @patient_type_old = patient_type, @charge_state_old = charge_state from Deleted
if :new.patient_type = 1 OR :new.patient_type = 3 then
if (:old.charge_state = 0 AND :new.charge_state = 1) OR (:old.charge_state = - 1 AND :new.charge_state = 1) OR (:old.charge_state = 1 AND :new.charge_state = - 1) then
ll_bqxh := :new.his_id;
if ll_bqxh <= 0 then
raise_application_error(-20001,'取病区序号失败');
return;
--ROLLBACK TRANSACTION
--RAISERROR('取病区序号失败',16,1)
end if;
--SELECT @zyh = zyh,@brxz = brxz, @cypb = cypb, @xgpb = xgpb, @zlxz = zlxz FROM zy_brry,Inserted WHERE zyh = Inserted.inpatient_id
SELECT ZYH,BRXZ,CYPB,XGPB,ZLXZ
into ll_zyh,ll_brxz,ll_cypb,ll_xgpb,ll_zlxz
FROM ZY_BRRY WHERE ZYH = :NEW.inpatient_id;
if ll_cypb >= 8 or ll_xgpb = 9 or ll_xgpb = 2 then
raise_application_error(-20001,'当前病人已经出院或正在进行结算或正在进行费别转换,不能记费');
return;
--ROLLBACK TRAN
--RAISERROR('当前病人已经出院或正在进行结算或正在进行费别转换,不能记费',16,1)
end if;
SELECT tzsj INTO ldt_tzsj FROM zy_bqyz WHERE jlxh = ll_bqxh;
SELECT sysdate into ldt_qrsj FROM dual;
open tc_cursor;
loop
fetch tc_cursor into ll_xmxh;
exit when tc_cursor%notfound; UPDATE gy_identity_zy SET dqz = dqz + 1 WHERE bmc = 'ZY_FYMX';
SELECT dqz into ll_jlxh FROM gy_identity_zy WHERE bmc = 'ZY_FYMX';
SELECT fygb,fymc,fydj into ll_fyxm,ls_fymc,ld_fydj FROM gy_ylsf WHERE fyxh = ll_xmxh;
SELECT COUNT(*) into ll_sum1 FROM gy_fyjy WHERE brxz = ll_brxz AND fyxh = ll_xmxh;
ld_zfbl := 100;
if ll_sum1 = 1 then
SELECT zfbl into ld_zfbl FROM gy_fyjy WHERE brxz = ll_brxz AND fyxh = ll_xmxh;
else
ld_zfbl := 100;
SELECT zfbl into ld_zfbl FROM gy_zfbl WHERE brxz = ll_brxz AND sfxm = ll_fyxm;
end if;
ld_zfbl := ld_zfbl / 100;
ld_zjje := ld_fydj;
ld_fysl := 1;
if (:old.charge_state = 1 AND :new.charge_state = - 1) then
ld_fysl := - 1;
ld_zjje := ld_zjje * ld_fysl;
end if;
ld_zfje := ld_zjje * ld_zfbl;
-- INSERT INTO zy_fymx
-- ( jlxh, zyh, fyrq, jfrq, fyks, fybq, zxks, fyxh, fymc,fyxm, xmlx, yplx, ypcd,
-- fysl, fydj, zfbl, zjje, zfje, zlje, srgh, qrgh, ysgh,yzxh, jscs, zlxz)
-- VALUES( ll_jlxh, ll_zyh,ldt_requisition_time, ldt_charge_time, ls_patient_dept,ls_patient_ward, ls_execute_dept, ll_xmxh, ls_fymc, ll_fyxm,
-- 3, 0, 0, ld_fysl, ld_fydj, ld_zfbl, ld_zjje, ld_zfje, 0, ls_requisition_person, ls_execute_person,ls_requisition_person, ll_bqxh, 0, ll_zlxz);
INSERT INTO zy_fymx
( jlxh, zyh, fyrq, jfrq, fyks, fybq, zxks, fyxh, fymc,fyxm, xmlx, yplx, ypcd,
fysl, fydj, zfbl, zjje, zfje, zlje, srgh, qrgh, ysgh,yzxh, jscs, zlxz)
VALUES( ll_jlxh, ll_zyh,:new.requisition_time, :new.charge_time, :new.patient_dept,:new.patient_ward, :new.execute_dept, ll_xmxh, ls_fymc, ll_fyxm,
3, 0, 0, ld_fysl, ld_fydj, ld_zfbl, ld_zjje, ld_zfje, 0, ls_requisition_person, ls_execute_person,ls_requisition_person, ll_bqxh, 0, ll_zlxz);
end loop;
end if;
close tc_cursor; if ( :old.charge_state = 0 or :old.charge_state = -1 ) AND :new.charge_state = 1 then
if ldt_tzsj is null then
UPDATE zy_bqyz SET qrsj = ldt_qrsj, sybz = 0 WHERE jlxh = ll_bqxh; else
UPDATE zy_bqyz SET qrsj = ldt_qrsj, sybz = 0, lsbz = 1 WHERE jlxh = ll_bqxh;
end if;
end if;
end if;END
;
after update of CHARGE_STATE on HIS_REQUISITION
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROWdeclare
ll_jlxh numeric(18,0);
ll_bqxh numeric(18,0);
ll_fyxm numeric(4,0);
ll_zyh numeric(18,0);
ll_brxz numeric(4,0);
ll_cypb numeric(2,0);
ll_xgpb numeric(1,0);
ld_zfbl numeric(6,2);
ld_zjje numeric(12,2);
ld_fydj numeric(12,2);
ld_zfje numeric(12,2);
ld_fysl numeric(10,2);
ll_zlxz numeric(4,0);
ll_sum1 numeric(2,0);
ll_xmxh numeric(6,0);
ldt_tzsj date;
ldt_qrsj date;
ls_fymc varchar(60);
ls_execute_person varchar(20);
ls_requisition_person varchar(20);
cursor tc_cursor is select jy_ztmx.fyxh from jy_ztmx where jy_ztmx.xmxh = :new.charge_item_id;
--搞清楚his_requisition.his_id是什么
--搞清楚his_requisition.charge_item_id是什么
Begin
--select ll_his_id = his_id ,ldt_requisition_time = requisition_time,ldt_charge_time = charge_time,ll_patient_dept = patient_dept,ls_patient_ward = patient_ward,@execute_dept = execute_dept,@execute_person = execute_person,@requisition_person = requisition_person,@patient_type = patient_type,@charge_state = charge_state from Inserted
--select @patient_type_old = patient_type, @charge_state_old = charge_state from Deleted
if :new.patient_type = 1 OR :new.patient_type = 3 then
if (:old.charge_state = 0 AND :new.charge_state = 1) OR (:old.charge_state = - 1 AND :new.charge_state = 1) OR (:old.charge_state = 1 AND :new.charge_state = - 1) then
ll_bqxh := :new.his_id;
if ll_bqxh <= 0 then
raise_application_error(-20001,'取病区序号失败');
return;
--ROLLBACK TRANSACTION
--RAISERROR('取病区序号失败',16,1)
end if;
--SELECT @zyh = zyh,@brxz = brxz, @cypb = cypb, @xgpb = xgpb, @zlxz = zlxz FROM zy_brry,Inserted WHERE zyh = Inserted.inpatient_id
SELECT ZYH,BRXZ,CYPB,XGPB,ZLXZ
into ll_zyh,ll_brxz,ll_cypb,ll_xgpb,ll_zlxz
FROM ZY_BRRY WHERE ZYH = :NEW.inpatient_id;
if ll_cypb >= 8 or ll_xgpb = 9 or ll_xgpb = 2 then
raise_application_error(-20001,'当前病人已经出院或正在进行结算或正在进行费别转换,不能记费');
return;
--ROLLBACK TRAN
--RAISERROR('当前病人已经出院或正在进行结算或正在进行费别转换,不能记费',16,1)
end if;
SELECT tzsj INTO ldt_tzsj FROM zy_bqyz WHERE jlxh = ll_bqxh;
SELECT sysdate into ldt_qrsj FROM dual;
open tc_cursor;
loop
fetch tc_cursor into ll_xmxh;
exit when tc_cursor%notfound; UPDATE gy_identity_zy SET dqz = dqz + 1 WHERE bmc = 'ZY_FYMX';
SELECT dqz into ll_jlxh FROM gy_identity_zy WHERE bmc = 'ZY_FYMX';
SELECT fygb,fymc,fydj into ll_fyxm,ls_fymc,ld_fydj FROM gy_ylsf WHERE fyxh = ll_xmxh;
SELECT COUNT(*) into ll_sum1 FROM gy_fyjy WHERE brxz = ll_brxz AND fyxh = ll_xmxh;
ld_zfbl := 100;
if ll_sum1 = 1 then
SELECT zfbl into ld_zfbl FROM gy_fyjy WHERE brxz = ll_brxz AND fyxh = ll_xmxh;
else
ld_zfbl := 100;
SELECT zfbl into ld_zfbl FROM gy_zfbl WHERE brxz = ll_brxz AND sfxm = ll_fyxm;
end if;
ld_zfbl := ld_zfbl / 100;
ld_zjje := ld_fydj;
ld_fysl := 1;
if (:old.charge_state = 1 AND :new.charge_state = - 1) then
ld_fysl := - 1;
ld_zjje := ld_zjje * ld_fysl;
end if;
ld_zfje := ld_zjje * ld_zfbl;
-- INSERT INTO zy_fymx
-- ( jlxh, zyh, fyrq, jfrq, fyks, fybq, zxks, fyxh, fymc,fyxm, xmlx, yplx, ypcd,
-- fysl, fydj, zfbl, zjje, zfje, zlje, srgh, qrgh, ysgh,yzxh, jscs, zlxz)
-- VALUES( ll_jlxh, ll_zyh,ldt_requisition_time, ldt_charge_time, ls_patient_dept,ls_patient_ward, ls_execute_dept, ll_xmxh, ls_fymc, ll_fyxm,
-- 3, 0, 0, ld_fysl, ld_fydj, ld_zfbl, ld_zjje, ld_zfje, 0, ls_requisition_person, ls_execute_person,ls_requisition_person, ll_bqxh, 0, ll_zlxz);
INSERT INTO zy_fymx
( jlxh, zyh, fyrq, jfrq, fyks, fybq, zxks, fyxh, fymc,fyxm, xmlx, yplx, ypcd,
fysl, fydj, zfbl, zjje, zfje, zlje, srgh, qrgh, ysgh,yzxh, jscs, zlxz)
VALUES( ll_jlxh, ll_zyh,:new.requisition_time, :new.charge_time, :new.patient_dept,:new.patient_ward, :new.execute_dept, ll_xmxh, ls_fymc, ll_fyxm,
3, 0, 0, ld_fysl, ld_fydj, ld_zfbl, ld_zjje, ld_zfje, 0, ls_requisition_person, ls_execute_person,ls_requisition_person, ll_bqxh, 0, ll_zlxz);
end loop;
end if;
close tc_cursor; if ( :old.charge_state = 0 or :old.charge_state = -1 ) AND :new.charge_state = 1 then
if ldt_tzsj is null then
UPDATE zy_bqyz SET qrsj = ldt_qrsj, sybz = 0 WHERE jlxh = ll_bqxh; else
UPDATE zy_bqyz SET qrsj = ldt_qrsj, sybz = 0, lsbz = 1 WHERE jlxh = ll_bqxh;
end if;
end if;
end if;END
;
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[F1] [int] NULL ,
[F2] [int] NULL ,
[F3] [int] NULL ,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[FID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
create TRIGGER updatetest ON [dbo].[TEST]
FOR INSERT, UPDATE, DELETE
AS
begin
declare @F1 int,
@FID int,
@OldF1 int
if update(F1)
begin
select @OldF1=F1 from test where FID in (select FID from INSERTED)
select @FID=FID,@F1=F1 from INSERTED
PRINT 'fID = ' + convert(varchar(10),@FID)
PRINT 'OldF1 = ' + convert(varchar(10),@OldF1)
PRINT 'F1 = ' + convert(varchar(10),@F1)
endendgoinsert test(f1,f2,f3) values(1,2,3)
insert test(f1,f2,f3) values(1,2,3)
insert test(f1,f2,f3) values(1,2,3)
go
select * from test
go
update test set f1=11 where fid=1
go
select * from test
go