tr_updatepersoninfo.sql

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

CREATE TRIGGER dbo.tr_updatepersoninfo ON dbo.personinfo 
   FOR INSERT, UPDATE AS 
        declare @m1_o numeric(8,2),@m2_o numeric(8,2),@m3_o numeric(8,2),@m4_o numeric(8,2),@m5_o numeric(8,2),@m6_o numeric(8,2),@m7_o numeric(8,2),@m8_o numeric(8,2),@m9_o numeric(8,2),@m10_o numeric(8,2),@m11_o numeric(8,2),@m12_o numeric(8,2) 
        declare @m1_n numeric(8,2),@m2_n numeric(8,2),@m3_n numeric(8,2),@m4_n numeric(8,2),@m5_n numeric(8,2),@m6_n numeric(8,2),@m7_n numeric(8,2),@m8_n numeric(8,2),@m9_n numeric(8,2),@m10_n numeric(8,2),@m11_n numeric(8,2),@m12_n numeric(8,2) 
        declare @dis_o numeric(8,2),@dis_n numeric(8,2)
        declare @d1 smalldatetime,@d2 smalldatetime,@d3 smalldatetime,@d4 smalldatetime,@d5 smalldatetime,@d6 smalldatetime,@d7 smalldatetime,@d8 smalldatetime,@d9 smalldatetime,@d10 smalldatetime,@d11 smalldatetime,@d12 smalldatetime  
        declare @personcode char(12)
        declare @bookindate smalldatetime,@audityear smalldatetime,@date_ini smalldatetime,@thisaddmonth smalldatetime
        declare @thisadd numeric(8,2)
        declare @rows int,@discount int,@month_jg int
        declare @name char(16)

    if not update(updateflag)    
      begin
 	   declare inserted_curs3 cursor for
	   select personcode
	   from inserted
       
	   open inserted_curs3
	   fetch  inserted_curs3 into 
               @personcode
               
	   if @@sqlstatus = 2 
	      begin
            close inserted_curs3
            deallocate cursor inserted_curs3
            rollback tran
            return 
	      end       
          
	    while (@@sqlstatus = 0 ) 
           begin  
               update personinfo set updateflag = '111111111111111111111111111111111111111111111111111111111111' where personcode=@personcode
                if @@error <> 0 
                     begin
                       raiserror 33333 "错误"  
                       close inserted_curs3 
                       deallocate cursor inserted_curs3
                       rollback transaction
                       return
                     end
	          fetch  inserted_curs3 into @personcode
           end

         close inserted_curs3 
         deallocate cursor inserted_curs3
       end

   IF UPDATE (thisadd)
      BEGIN
        
        select @rows = @@rowcount
        select @date_ini = convert(smalldatetime,'1900/01/01',111)
        select @thisaddmonth = getdate()

	   declare inserted_curs cursor for
	   select personcode,name,bookindate,audityear,thisadd
	   from inserted
       
	   open inserted_curs
	   fetch  inserted_curs into 
               @personcode,@name,@bookindate,@audityear,@thisadd
               
	   if @@sqlstatus = 2 
	      begin
            close inserted_curs
            deallocate cursor inserted_curs
            rollback tran
            return 
	      end       
          
	    while (@@sqlstatus = 0 )   
	        begin
              select @dis_o = 0
              select @dis_n = 0
              select @discount = 0
              select @discount=count(personcode) from dislist where personcode = @personcode and audityear = @audityear
              select @month_jg = (datepart(yy,@audityear)-datepart(yy,@bookindate))*12+(datepart(mm,@audityear)-datepart(mm,@bookindate))

              if @discount = 1
                begin
                  select @m1_o=m1,@m2_o=m2,@m3_o=m3,@m4_o=m4,@m5_o=m5,@m6_o=m6,@m7_o=m7,@m8_o=m8,@m9_o=m9,@m10_o=m10,@m11_o=m11,@m12_o=m12,@d1=disdate1,@d2=disdate2,@d3=disdate3,@d4=disdate4,@d5=disdate5,@d6=disdate6,@d7=disdate7,@d8=disdate8,@d9=disdate9,@d10=disdate10,@d11=disdate11,@d12=disdate12
                  from dislist where personcode=@personcode and audityear=@audityear
                  
                  if @month_jg >=0 
                    begin
                      select @m1_n=round(@thisadd / 12,1)
                      select @m2_n=round(@thisadd / 12,1)
                      select @m3_n=round(@thisadd / 12,1)
                      select @m4_n=round(@thisadd / 12,1)
                      select @m5_n=round(@thisadd / 12,1)
                      select @m6_n=round(@thisadd / 12,1)
                      select @m7_n=round(@thisadd / 12,1)
                      select @m8_n=round(@thisadd / 12,1)
                      select @m9_n=round(@thisadd / 12,1)
                      select @m10_n=round(@thisadd / 12,1)
                      select @m11_n=round(@thisadd / 12,1)
                      select @m12_n=@thisadd - round(@thisadd / 12,1)*11
                    end

                  if @month_jg = -1 
                    begin
                      select @m1_n=0
                      select @m2_n=round(@thisadd / 11,1)
                      select @m3_n=round(@thisadd / 11,1)
                      select @m4_n=round(@thisadd / 11,1)
                      select @m5_n=round(@thisadd / 11,1)
                      select @m6_n=round(@thisadd / 11,1)
                      select @m7_n=round(@thisadd / 11,1)
                      select @m8_n=round(@thisadd / 11,1)
                      select @m9_n=round(@thisadd / 11,1)
                      select @m10_n=round(@thisadd / 11,1)
                      select @m11_n=round(@thisadd / 11,1)
                      select @m12_n=@thisadd - round(@thisadd / 11,1)*10
                    end
                 if @month_jg = -2 
                     begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=round(@thisadd / 10,1)
                      select @m4_n=round(@thisadd / 10,1)
                      select @m5_n=round(@thisadd / 10,1)
                      select @m6_n=round(@thisadd / 10,1)
                      select @m7_n=round(@thisadd / 10,1)
                      select @m8_n=round(@thisadd / 10,1)
                      select @m9_n=round(@thisadd / 10,1)
                      select @m10_n=round(@thisadd / 10,1)
                      select @m11_n=round(@thisadd / 10,1)
                      select @m12_n=@thisadd - round(@thisadd / 10,1)*9
                    end
                  if @month_jg = -3 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=round(@thisadd / 9,1)
                      select @m5_n=round(@thisadd / 9,1)
                      select @m6_n=round(@thisadd / 9,1)
                      select @m7_n=round(@thisadd / 9,1)
                      select @m8_n=round(@thisadd / 9,1)
                      select @m9_n=round(@thisadd / 9,1)
                      select @m10_n=round(@thisadd / 9,1)
                      select @m11_n=round(@thisadd / 9,1)
                      select @m12_n=@thisadd - round(@thisadd / 9,1)*8
                    end
                  if @month_jg = -4 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=round(@thisadd / 8,1)
                      select @m6_n=round(@thisadd / 8,1)
                      select @m7_n=round(@thisadd / 8,1)
                      select @m8_n=round(@thisadd / 8,1)
                      select @m9_n=round(@thisadd / 8,1)
                      select @m10_n=round(@thisadd / 8,1)
                      select @m11_n=round(@thisadd / 8,1)
                      select @m12_n=@thisadd - round(@thisadd / 8,1)*7
                    end
                  if @month_jg = -5 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=round(@thisadd / 7,1)
                      select @m7_n=round(@thisadd / 7,1)
                      select @m8_n=round(@thisadd / 7,1)
                      select @m9_n=round(@thisadd / 7,1)
                      select @m10_n=round(@thisadd / 7,1)
                      select @m11_n=round(@thisadd / 7,1)
                      select @m12_n=@thisadd - round(@thisadd / 7,1)*6
                    end
                  if @month_jg = -6 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=0
                      select @m7_n=round(@thisadd / 6,1)
                      select @m8_n=round(@thisadd / 6,1)
                      select @m9_n=round(@thisadd / 6,1)
                      select @m10_n=round(@thisadd / 6,1)
                      select @m11_n=round(@thisadd / 6,1)
                      select @m12_n=@thisadd - round(@thisadd / 6,1)*5
                    end
                  if @month_jg = -7 
                    begin
                      select @m1_n=0
                      select @m2_n=0
                      select @m3_n=0
                      select @m4_n=0
                      select @m5_n=0
                      select @m6_n=0
                      select @m7_n=0
                      select @m8_n=round(@thisadd / 5,1)
                      select @m9_n=round(@thisadd / 5,1)
                      select @m10_n=round(@thisadd / 5,1)
                      select @m11_n=round(@thisadd / 5,1)
                      select @m12_n=@thisadd - round(@thisadd / 5,1)*4
                    end
                  if @month_jg = -8