?? tr_insertmcrecord.sql
字號(hào):
CREATE TRIGGER dbo.tr_insertmcrecord ON dbo.mcrecord
FOR INSERT AS
/*規(guī)則:1.從個(gè)人信息表中的個(gè)人帳戶余額thisleave中減去IC卡支付cardcons
累加IC卡支付cardcons到selfconsume
累加合規(guī)費(fèi)用fundadd(門(mén)診時(shí)為0)到suminhos
更新thisaddmonth
2.如果是住院,則inhos加1
*/
BEGIN
declare @rows int,@inhos tinyint /*住院累加器*/
declare @sicktype tinyint
declare @personcode char(12),@checker char(10)
declare @fundadd numeric(14,2),@cardcons numeric(8,2),@fundcons numeric(8,2),@n_checker numeric(8,2)
declare @thisaddmonth smalldatetime
select @rows = @@rowcount
select @inhos = 0 /*初始化累加器*/
select @thisaddmonth=getdate()
if @rows = 0
return
declare inserted_curs cursor for
select personcode,sicktype,cardcons,fundadd,fundcons,checker
from inserted
open inserted_curs
fetch inserted_curs into
@personcode,@sicktype,@cardcons,@fundadd,@fundcons,@checker
if @@sqlstatus = 2
begin
close inserted_curs
deallocate cursor inserted_curs
rollback tran
return
end
while (@@sqlstatus = 0 )
begin
if @sicktype = 2 or @sicktype = 3 /*縣內(nèi)住院:2,異地住院:3*/
begin
select @inhos = 1 /*如果是住院則累加器加1*/
select @n_checker = convert(numeric(8,2),@checker) /*統(tǒng)籌支付中的超額*/
end
update personinfo
set thisleave = (isnull(thisleave,0) - isnull(@cardcons,0)), /*核減個(gè)人帳戶*/
selfconsume = (isnull(selfconsume,0) + isnull(@cardcons,0)), /*累計(jì)個(gè)人帳戶支付*/
sumclinic = (isnull(sumclinic,0) + isnull(@n_checker,0)),
suminhos = (isnull(suminhos,0) + isnull(@fundadd,0)), /*累加合規(guī)費(fèi)用*/
sumsick = (isnull(sumsick,0) + isnull(@fundcons,0)), /*累加統(tǒng)籌支付*/
inhosorder = (isnull(inhosorder,0) + @inhos), /*累加住院次數(shù)*/
thisaddmonth = @thisaddmonth, /*更新個(gè)帳時(shí)間*/
updateflag ='111111111111111111111111111111111111111111111111111111111111' /*通知各醫(yī)院下載*/
where (personcode = @personcode)
if @@error <> 0
begin
raiserror 33333 "錯(cuò)誤"
close inserted_curs
deallocate cursor inserted_curs
rollback transaction
return
end
fetch inserted_curs into
@personcode,@sicktype,@cardcons,@fundadd,@fundcons,@checker
end
close inserted_curs
deallocate cursor inserted_curs
END
?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -