sql 学习记录

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

阅读更多
文章标签: sql insert null
下一篇简单的问题
想对作者说点什么? 我来说一句

数据库开发规范

2011年11月16日 42KB 下载

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

关闭
关闭
关闭