LearnCharp的专栏

我得学习记录

简单的问题

1.  搜索统一字段中所有的同名记录
      select loginid from systemusers group by loginid having count(loginid)>1

2.  数据触发器应用:Insert  >   当数据为空时(NULL)将数据转变为同列中最大值加 1
 
CREATE trigger test_insert

on dbo._OA_HJXM

instead of insert

as

begin 
 
insert into _OA_HJXM (Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,Number) 

  select  Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,

      case when number is null

              1.then (select isnull(count(*),0)+1 from  _OA_HJXM)

              2.then (select isnull(max(number),0)+1 from  _OA_HJXM)

           else number
      
end 
 from inserted

end

on dbo._OA_HJXM

instead of insert

as

begin 
 
insert into _OA_HJXM (Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,Number) 

  select  Creater,CreateTime,ShareFlag,Deleted,FlowStatus,EndFlag,SystemText,SerialNO,HJYear,ProjName,HJLevel,Memo,Unit,Member,Depart,ProjType,Photo,

      case when number is null

              1.then (select isnull(count(*),0)+1 from  _OA_HJXM)

              2.then (select isnull(max(number),0)+1 from  _OA_HJXM)

           else number
      
end 
 from inserted

end

阅读更多
文章标签: insert null
个人分类: SQL Server 学习记录
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭