tr_insertmcrecord.sql

十分好的一个医院管理系统

CREATE TRIGGER dbo.tr_insertmcrecord ON dbo.mcrecord 
   FOR INSERT AS 
        /*规则：1.从个人信息表中的个人帐户余额thisleave中减去IC卡支付cardcons
	          累加IC卡支付cardcons到selfconsume
	          累加合规费用fundadd(门诊时为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  /*县内住院：2，异地住院：3*/
                    begin
                        select @inhos = 1       /*如果是住院则累加器加1*/
                        select @n_checker = convert(numeric(8,2),@checker)   /*统筹支付中的超额*/
                    end

	            update personinfo 
	            set  thisleave = (isnull(thisleave,0) -  isnull(@cardcons,0)),  /*核减个人帐户*/
		    selfconsume = (isnull(selfconsume,0) + isnull(@cardcons,0)),   /*累计个人帐户支付*/
                    sumclinic = (isnull(sumclinic,0) + isnull(@n_checker,0)),  
                    suminhos = (isnull(suminhos,0) + isnull(@fundadd,0)),    /*累加合规费用*/
                    sumsick = (isnull(sumsick,0) + isnull(@fundcons,0)),   /*累加统筹支付*/
                    inhosorder = (isnull(inhosorder,0) + @inhos),   /*累加住院次数*/
                    thisaddmonth = @thisaddmonth,   /*更新个帐时间*/
                    updateflag ='111111111111111111111111111111111111111111111111111111111111'  /*通知各医院下载*/
	            where (personcode = @personcode)

	            if @@error <> 0 
	               begin
		              raiserror 33333 "错误"  
                      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