tr_updatemrlist.sql

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

CREATE TRIGGER dbo.tr_updatemrlist ON dbo.mcrecordlist 
   FOR UPDATE AS 

   IF ( UPDATE (tollitem) or UPDATE (quantity) or UPDATE (itemfee) )
      BEGIN
        declare @rows int 
        declare @hoscode char(4),
	        @rcode char(12),
	        @advdate datetime,
                @seqno smallint
        /*检查更新行数*/
         select @rows = @@rowcount
         if @rows = 0 
             return
	/*定义光标*/
	 declare p_inserted_curs cursor for
	 select hoscode,riceiptcode,advdate,seqno
	 from inserted
	 /*打开光标*/
	 open p_inserted_curs
            
         fetch p_inserted_curs into
            @hoscode,@rcode,@advdate,@seqno
	 /*检查光标打开情况*/
         if @@sqlstatus = 2 
             begin
                deallocate cursor p_inserted_curs 
                rollback transaction
                return
             end 
	  /*使用光标更新updateflag*/
          while(@@sqlstatus = 0)
            begin          
               update mcrecordlist 
               set updateflag = '1'
               where hoscode = @hoscode
	             and riceiptcode = @rcode
                     and advdate = @advdate
		     and seqno = @seqno
		 /*更新出错的处理*/
                if @@error <> 0 
	           begin
                        deallocate cursor p_inserted_curs 
	                raiserror 33333 "错误"
                        rollback transaction
                        return
                    end 	  
	       fetch p_inserted_curs into
               @hoscode,@rcode,@advdate,@seqno
            
               if @@sqlstatus = 1 
                  begin
                      deallocate cursor p_inserted_curs 
		      raiserror 33333 "错误"
                      rollback transaction
                      return
                   end
            end /*end while */
   if @@error <> 0 
	begin
	  deallocate cursor p_inserted_curs 
	  raiserror 33333 "错误"
          rollback transaction
          return
          end 	     
   deallocate cursor p_inserted_curs 
    END