一个存储过程，触发器

存储过程：

    use lms;
    if (object_id('Proc_MouseWheel', 'P') is not null)
    drop proc Proc_MouseWheel
    go
    create proc Proc_MouseWheel (
    @currentPage decimal(18,4),
    @totalPage decimal(18,4),
    @currentIndex decimal(18,4),
    @user_id int,
    @chapter_id int)
    as
    declare @maxIndex decimal(18,4);
    select @maxIndex=maxIndex from lmsCourseChapterHumanRelationship where user_id=@user_id and chapter_id=@chapter_id;
    if(@maxIndex is null)
        set @maxIndex=0;
    if(@maxIndex<@currentPage)
        set @maxIndex=@currentPage;
    if(@maxIndex>@totalPage)
        set @maxIndex=@totalPage;
    update lmsCourseChapterHumanRelationship set totalPage=@totalPage,currentPage=@currentPage,currentIndex=@currentIndex,maxIndex=@maxIndex where user_id=@user_id and chapter_id=@chapter_id;

触发器

use lms;
if (object_id('tgr_lmsCourseChapterHumanRelationship_update_column', 'TR') is not null)
    drop trigger tgr_lmsCourseChapterHumanRelationship_update_column
go
create trigger tgr_lmsCourseChapterHumanRelationship_update_column
on lmsCourseChapterHumanRelationship
    for update
as
      --定义变量
    declare @currentPage decimal(18,4), @totalPage decimal(18,4), @maxIndex decimal(18,4),@chapter_id int,@user_id int
    select @chapter_id=chapter_id,@user_id=user_id,@maxIndex=maxIndex from deleted;
    select @currentPage=currentPage,@totalPage=totalPage  from inserted;
 
    if(update(currentPage))
    begin
        if(@maxIndex is null)
        begin
          set @maxIndex=0;
        end

        if(@maxIndex<@currentPage)
        begin
        set @maxIndex=@currentPage;
        end

        if(@maxIndex>@totalPage)
        begin
        set @maxIndex=@totalPage;
        end
        
        update lmsCourseChapterHumanRelationship set totalPage=@totalPage,currentPage=@currentPage,maxIndex=@maxIndex where user_id=@user_id and chapter_id=@chapter_id;
    end
go

 嵌套循环

use lms;
    declare @course_id int,@ids nvarchar(max),@id int,@num int,@num2 int ,@chapter_id int,@sql nvarchar(max);
    set @course_id=99;
    set @ids='1302,1303,1304,1305,1306,1307,1308,1309,1310,1311,1312,1313,1314,1315,1316,1317,1318,1319,1320,1321,1322,1323,1324,1325,1326,1327,1328,1329,1330';
    set @ids=@ids+',111';
    select @num2=count(1) from lmsChapter where course_id=@course_id;
    select ROW_NUMBER() over(order by chapter_id) as id, * into #temp from lmsChapter where course_id=@course_id;
    while(charindex(',',@ids)>0)
    begin
        set @id=SUBSTRING(@ids,1,charindex(',',@ids)-1)  
        select @ids=SUBSTRING(@ids,charindex(',',@ids)+1,LEN(@ids))
        select @num=Count(1) from lmsCourseChapterHumanRelationship where course_id=@course_id and user_id=@id;
        if(@num=0)
        begin
            if(@num2>0)
            begin
               declare @i int;
               set @i=1;
                while(@i<=@num2)
                begin
                    select @chapter_id=chapter_id from #temp where id=@i;
                    set @i=@i+1;
                    insert into lmsCourseChapterHumanRelationship (course_id,chapter_id,user_id) values (@course_id,@chapter_id,@id);
                end
            end
        end
    end
    drop table #temp;

 

use lms;
    if (object_id('Proc_PutHumanInCourse', 'P') is not null)
    drop proc Proc_PutHumanInCourse
    go
    create proc Proc_PutHumanInCourse (
    @course_id int,
    @ids nvarchar(max),
    @returnNum int out
    )
    as
    declare @id int,@num int,@num2 int ,@chapter_id int,@sql nvarchar(max);
    set @ids=@ids+',111';
    set @returnNum=1;
    select @num2=count(1) from lmsChapter where course_id=@course_id;
    select ROW_NUMBER() over(order by chapter_id) as id, * into #temp from lmsChapter where course_id=@course_id;
    while(charindex(',',@ids)>0)
    begin
        set @id=SUBSTRING(@ids,1,charindex(',',@ids)-1)  
        select @ids=SUBSTRING(@ids,charindex(',',@ids)+1,LEN(@ids))
        select @num=Count(1) from lmsCourseChapterHumanRelationship where course_id=@course_id and user_id=@id;
        if(@num=0)
        begin
            if(@num2>0)
            begin 
               set @returnNum=@returnNum+1;
               declare @i int;
               set @i=1;
                while(@i<=@num2)
                begin
                    select @chapter_id=chapter_id from #temp where id=@i;
                    set @i=@i+1;
                    insert into lmsCourseChapterHumanRelationship (course_id,chapter_id,user_id) values (@course_id,@chapter_id,@id);
                end
            end
        end
    end
    drop table #temp;
 

 

 public int ImportPersonData(string user_ids, int course_id)
        {
            IDataParameter[] parameters = new SqlParameter[8];
            parameters[0] = new SqlParameter("@course_id", SqlDbType.Int);
            parameters[1] = new SqlParameter("@ids", SqlDbType.NVarChar, 4000);
            parameters[2] = new SqlParameter("@returnNum", SqlDbType.Int);


            parameters[0].Value = course_id;
            parameters[1].Value = user_ids;
            parameters[2].Direction = ParameterDirection.Output;
            DbHelperSQL.RunProcedure("Proc_PutHumanInCourse", parameters);
            int rows = Convert.ToInt32(parameters[2].Value.ToString());
            return rows;
        }

 

http://www.cnblogs.com/Donkey/archive/2010/07/13/1776780.html

 http://www.cnblogs.com/yfdong22/archive/2012/09/12/2681432.html