row_number sql server 2005 更新数据

背景： 项目中遇到这样一个问题 ，原来的一些应用数据库设计时，有的表没有主键。我们需要手动添加主键 而且不能是自动增长的。

 

一共需要做这些事情 ：

 

1. 添加字段。不为NULL

2.更新数据

3.设为主键。

 

为了不影响原来的程序。我写了一些触发器 来实现自动增长，而且对原来的应用没有任何影响。

 

为了更新数据，我们单独写了一个存储过程来实现数据的更新。 数据更新的困难在于要生成唯一的值，而且更新一条记录。

考虑多个方案，后来想到 row_number  sql server 2005  相当于oracle 的rowid

 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter PROCEDURE [dbo].[zfhf_letter_pro]

as

 

declare @rowid int

declare  zfhf_letter_cursor cursor for SELECT  ROW_NUMBER() OVER (ORDER BY sublet_id ASC) AS ROWID  FROM zfhf_letter

open zfhf_letter_cursor

fetch next from zfhf_letter_cursor into @rowid

WHILE @@FETCH_STATUS = 0

begin

 

with CTE
as
(
  select rw=row_number() over(order by sublet_id),*
  from zfhf_letter
)
update CTE
  set TONGBUID=@rowid
 where rw=@rowid

fetch next from zfhf_letter_cursor into @rowid

end
close zfhf_letter_cursor

deallocate zfhf_letter_cursor

 

 

 

这是存储过程的全部

 

感兴趣的同学 可以看看

 

 

 

下面是自动增长替代的那个触发器

 

 

create trigger zfhf_reply_tri

--应用到的表

on [dbo].zfhf_reply

--触发事件

for insert

as

begin  

-- 表中存在主键为的记录

        if exists(select * from zfhf_reply  where recid=0)

        begin

          delete from zfhf_reply  where recid=0

        end

 

--变量定义

  declare @myID as int

  declare @myLet_unit_id as int

  declare @myReplytitle as varchar(200)  

  declare @myReplycontent as varchar(5000) 

  declare @myreplydate as datetime

  declare @myReplyer as varchar(200) 

  declare @myHigher_up as varchar(50) 

  declare @myReplyTel as varchar(100) 

--变量赋值

 

  select @myID = recid  from inserted

    if(@myID=0)

       begin

      

 select @myID = max(recid) from zfhf_reply 

       end

    else

--如何默认有值

       begin

          

    set @myID=0

       end

 

   select @myLet_unit_id = let_unit_id from inserted

   select @myReplytitle = replytitle from inserted

   select @myReplycontent = replycontent from inserted

   select @myreplydate = replydate from inserted

   select @myReplyer = replyer from inserted

   select @myHigher_up = higher_up from inserted

   select @myReplyTel = replyTel from inserted

    --数据库没有记录的变量

      declare @noResult as int

      set @noResult=0

 

       --表中没有任何记录

        if not exists(select * from zfhf_reply)

        begin

           --变量定义

             declare @tempID as int

             declare @tempLet_unit_id as int

             declare @tempReplytitle as varchar(200)   

             declare @tempReplycontent as varchar(5000) 

             declare @tempreplydate as datetime

             declare @tempReplyer as varchar(200) 

             declare @tempHigher_up as varchar(50) 

             declare @tempReplyTel as varchar(100) 

             declare @tempName as varchar(20)   

           --变量赋值

              select @tempID = max(recid) from zfhf_reply 

              select @tempLet_unit_id = let_unit_id from inserted

              select @tempReplytitle = replytitle from inserted

              select @tempReplycontent = replycontent from inserted

              select @tempreplydate = replydate from inserted

              select @tempReplyer = replyer from inserted

              select @tempHigher_up = higher_up from inserted

              select @tempReplyTel = replyTel from inserted

 

                  insert into zfhf_reply(recid,let_unit_id,replytitle,replycontent,replydate,replyer,higher_up,replyTel)

               values(1,@tempLet_unit_id,@tempReplytitle,@tempReplycontent,@tempreplydate,@tempReplyer,@tempHigher_up,@tempReplyTel)

          set @noResult=1

 

return

        end

      

 

 

--数据入库

     if not exists(select * from zfhf_reply where recid=@myID+1)

        begin

         insert into zfhf_reply(recid,let_unit_id,replytitle,replycontent,replydate,replyer,higher_up,replyTel)

             values(@myID+1,@myLet_unit_id,@myReplytitle,@myReplycontent,@myreplydate,@myReplyer,@myHigher_up,@myReplyTel)

end

 

 

--删除插入的主键为默认值的记录在数据库不存在记录的情况下而插入的记录

       if(@noResult=1)

       begin

         delete from zfhf_reply where recid=1

       end

--删除主键为的数据insert 插入的主键为默认值的数据   

       delete from zfhf_reply where recid=0

    return

    end