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