根据ID生成字符串？

--创建测试表
create table usertable(userid varchar(20),username nvarchar(20))
go
--创建触发器
create trigger tg_insert on usertable
for insert
as
declare @username nvarchar(20)
declare @userid varchar(20)
declare @num int
select @userid=max(userid) from usertable where userid like 'jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '%'
if @userid is null
   set @userid='jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '-01'
else
   begin
     set @num=cast(substring(@userid,10,2) as int)
     set @num=@num + 1
     if @num<10
     set @userid='jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '-0' + cast(@num as varchar(2))
     else
     set @userid='jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '-' + cast(@num as varchar(2))
   end
select @username=username from inserted
rollback
insert into usertable values(@userid,@username)
go--测试数据
insert into usertable(username) values('aa')
go
insert into usertable(username) values('bb')
go
insert into usertable(username) values('cc')
go
--显示数据
select * from usertable
go

fengfangfang 的示例很精彩，其实楼主可以举一反三，写出一个函数

fengfangfang() 的触发器实际上是有问题的，如果是这样插入语句，看看结果是什么。insert usertable(username) Select 'dd'
Union All Select 'ee'

very good!
fengfangfang() 谢谢！
大作。

CREATE TABLE [S_MAXNO] (
[ID] [numeric](10, 0) NOT NULL ,
[T_NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[T_DES] [varchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
[T_PREFIEX] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[T_TYPE] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[T_YEAR] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[T_DATE] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[T_NUM] [numeric](18, 0) NULL ,
[T_FLAG] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
PRIMARY KEY  CLUSTERED
(
[ID],
[T_NAME]
)  ON [PRIMARY]
) ON [PRIMARY]
GOCREATE    PROCEDURE SP_BILLNO
(@TABLENAME VARCHAR(50),@BILLID CHAR(12) OUTPUT)
AS
DECLARE @STRDATE CHAR(10)
DECLARE @SDATE   CHAR(8)
DECLARE @STR CHAR(2)
DECLARE @SYEAR   CHAR(4)
DECLARE @COUNT   NUMERIC
DECLARE @STYPE   CHAR(1)
DECLARE @PREFIX  CHAR(2)/* 取得当年的年数 */
SELECT @STRDATE=CONVERT(CHAR(10),GETDATE(),120)
SELECT @SDATE=LEFT(@STRDATE,4)+RIGHT(LEFT(@STRDATE,7),2)+RIGHT(@STRDATE,2) ----得到8位日期
SELECT @SYEAR=LEFT(@STRDATE,4)
SELECT @STR=RIGHT(LEFT(@STRDATE,4),2)/* 检测是否存在 */
IF (@TABLENAME IS NULL) OR (@TABLENAME="")
BEGIN
   RETURN -1
ENDBEGIN TRAN ---=====================================
---   检测单号产生类型//单号前缀
---=====================================
---类型   描述          位数   说明
---1   PRE+YY+######   2+ 8   默认
---2   PRE+YYMM+####    2+8
---3   PRE+YYMMDD+####  2+10
---B   YYYYMMDD####     12    12条形码
---L   ############     12    12位的连续数字
---=====================================
IF (SELECT COUNT(*) FROM S_MAXNO WHERE  T_NAME=@TABLENAME)<=0
   BEGIN
      SELECT @COUNT=ISNULL(MAX([ID]),0)+1 FROM S_MAXNO
      INSERT INTO [DBO].[S_MAXNO]([ID], [T_NAME], [T_DES], [T_PREFIEX], [T_TYPE], [T_YEAR], [T_DATE], [T_NUM], [T_FLAG])
      VALUES(@COUNT, @TABLENAME,'<无描述>', '', '1', @SYEAR, @STRDATE, 0, '1')
   END
SELECT @PREFIX=RTRIM(ISNULL(T_PREFIEX,'')),@STYPE=RTRIM(ISNULL(T_TYPE,'')) FROM  S_MAXNO WHERE  T_NAME=@TABLENAME
---检测没有产生类型时，采用默认类型
IF @STYPE=''
   BEGIN
      SELECT @STYPE='1'
      UPDATE  S_MAXNO SET T_TYPE='1' WHERE  T_NAME=@TABLENAME
   ENDIF @STYPE='1' --PRE+YY+######
   BEGIN
      /* 检测本年份是否有记录 */
      IF(SELECT COUNT(*) FROM S_MAXNO WHERE T_NAME=@TABLENAME AND T_YEAR=@SYEAR)<=0
         BEGIN
            UPDATE S_MAXNO SET T_YEAR=@SYEAR,T_DATE=@STRDATE,T_NUM=1   WHERE T_NAME=@TABLENAME
            SELECT @BILLID=RTRIM(@PREFIX)+@STR+"000001"
         END
      ELSE
         BEGIN
            UPDATE S_MAXNO SET T_NUM=ISNULL(T_NUM,0)+1   WHERE T_NAME=@TABLENAME
            SELECT @COUNT=ISNULL(T_NUM,0) FROM S_MAXNO WHERE T_NAME=@TABLENAME
            SELECT @BILLID=RTRIM(@PREFIX)+@STR+REPLICATE('0',6 - LEN(RTRIM(CONVERT(CHAR(6),@COUNT))))+RTRIM(CONVERT(CHAR(6),@COUNT))
         END
   END
IF @STYPE='2' --PRE+YYMM+####
   BEGIN
      /* 检测本月份是否有记录 */
      IF(SELECT COUNT(*) FROM S_MAXNO WHERE T_NAME=@TABLENAME AND LEFT(T_DATE,7)= LEFT(@STRDATE,7))<=0
         BEGIN
            UPDATE S_MAXNO SET T_YEAR=@SYEAR,T_DATE=@STRDATE,T_NUM=1   WHERE T_NAME=@TABLENAME
            SELECT @BILLID=RTRIM(@PREFIX)+@STR+RIGHT(LEFT(@STRDATE,7),2)+"0001"
         END
      ELSE
         BEGIN
            UPDATE S_MAXNO SET T_NUM=ISNULL(T_NUM,0)+1   WHERE T_NAME=@TABLENAME
            SELECT @COUNT=ISNULL(T_NUM,0) FROM S_MAXNO WHERE T_NAME=@TABLENAME
            SELECT @BILLID=RTRIM(@PREFIX)+@STR+RIGHT(LEFT(@STRDATE,7),2)+REPLICATE('0',4 - LEN(RTRIM(CONVERT(CHAR(6),@COUNT))))+RTRIM(CONVERT(CHAR(6),@COUNT))
         END
   END
IF @STYPE='3' ---PRE+YYMMDD+####
   BEGIN
      /* 检测本日份是否有记录 */
      IF(SELECT COUNT(*) FROM S_MAXNO WHERE T_NAME=@TABLENAME AND T_DATE= @STRDATE)<=0
         BEGIN
            UPDATE S_MAXNO SET T_YEAR=@SYEAR,T_DATE=@STRDATE,T_NUM=1   WHERE T_NAME=@TABLENAME
            SELECT @BILLID=RTRIM(@PREFIX)+RIGHT(@SDATE,6)+"0001"
         END
      ELSE
         BEGIN
            UPDATE S_MAXNO SET T_NUM=ISNULL(T_NUM,0)+1   WHERE T_NAME=@TABLENAME
            SELECT @COUNT=ISNULL(T_NUM,0) FROM S_MAXNO WHERE T_NAME=@TABLENAME
            SELECT @BILLID=RTRIM(@PREFIX)+RIGHT(@SDATE,6)+REPLICATE('0',4 - LEN(RTRIM(CONVERT(CHAR(6),@COUNT))))+RTRIM(CONVERT(CHAR(6),@COUNT))
         END
   END
IF @STYPE='B' --YYYYMMDD####
   BEGIN
      /* 检测本日份是否有记录 */
      IF(SELECT COUNT(*) FROM S_MAXNO WHERE T_NAME=@TABLENAME AND T_DATE= @STRDATE)<=0
         BEGIN
            UPDATE S_MAXNO SET T_YEAR=@SYEAR,T_DATE=@STRDATE,T_NUM=1   WHERE T_NAME=@TABLENAME
            SELECT @BILLID=@SDATE+"0001"
         END
      ELSE
         BEGIN
            UPDATE S_MAXNO SET T_NUM=ISNULL(T_NUM,0)+1   WHERE T_NAME=@TABLENAME
            SELECT @COUNT=ISNULL(T_NUM,0) FROM S_MAXNO WHERE T_NAME=@TABLENAME
            SELECT @BILLID=@SDATE+REPLICATE('0',4 - LEN(RTRIM(CONVERT(CHAR(6),@COUNT))))+RTRIM(CONVERT(CHAR(6),@COUNT))
         END
   END
IF @STYPE='L'
   BEGIN
      UPDATE S_MAXNO SET T_NUM=ISNULL(T_NUM,0)+1   WHERE T_NAME=@TABLENAME
      SELECT @COUNT=ISNULL(T_NUM,0) FROM S_MAXNO WHERE T_NAME=@TABLENAME
      SELECT @BILLID=REPLICATE('0',12 - LEN(RTRIM(CONVERT(CHAR(6),@COUNT))))+RTRIM(CONVERT(CHAR(6),@COUNT))
   END
/* 提交事务 */
COMMIT TRANIF(@@ERROR<>0)
BEGIN
  ROLLBACK TRAN
  RETURN -1
END
ELSE
BEGIN
  SELECT BILLID=@BILLID
END
GO
'用存储过程实现

有点问题哟，我用asp的conn.addnew .........conn.update为什么插入失败呢。

前面我已经说过，如果你一次只插入一条数据就可以用fengfangfang写的触发器，如果是同时插入多条就不行。既然你现在使用了他的触发器，是不是说明你一次只会插入一条数据？？那么，我给你写一个简单一点的。

--建立测试环境
Create table usertable(userid varchar(20) ,username Nvarchar(20))
GO
--建立触发器
Create Trigger Insertusertable On usertable
Instead Of Insert
As
Begin
    Declare @userid Varchar(100)
    Declare @username Nvarchar(1000)
    Select @userid=IsNull(('zjxd'+Right(Convert(Varchar(10),GetDate(),112),4)+'-'+Right(Right(Max(userid),2)+101,2)),('zjxd'+Right(Convert(Varchar(10),GetDate(),112),4)+'-01')) from usertable Where userid Like ('%'+Right(Convert(Varchar(10),GetDate(),112),4)+'%')
    Select @username=username from Inserted
    Insert usertable Values(@userid,@username)
End
GO
--测试
Insert usertable(username) Values(N'张三')
Insert usertable(username) Values(N'李四')
Insert usertable(username) Values(N'赵五')
Insert usertable(username) Values(N'钱三')
Select * from usertable
--删除测试环境
Drop Table usertable
--结果
/*
userid username
zjxd0607-01 张三
zjxd0607-02 李四
zjxd0607-03 赵五
zjxd0607-04 钱三
*/

好像和这个有关declare @username nvarchar(20)
我表中除了username外还有很多字段如sex,address 等，我定义他们为varchar(100)但是插入的时候，
只能insert into usertable(username,sex,address)values('aa','男','adfasdfasdf')这几个字段值如果多出两位就会出现
服务器: 消息 8152，级别 16，状态 2，过程 zjxdtr_insert，行 99
将截断字符串或二进制数据。
语句已终止。
但是不明白为已定义100，为什么还会限制字长

不行呀，怎么变也是这样。只能两位。帮忙测试，好吗？多谢！
if not exists(select * from PersionInfo  where (name=@name and name1=@name1) or (name=@name1 and name1=@name))
begin
insert into persioninfo(cnusername,[name],sex,age,zzmm,jg,sfz,school,profession,sstel,hometel,address,postcode,email,other,msn,
name1,sex1,age1,zzmm1,jg1,sfz1,school1,profession1,sstel1,hometel1,address1,postcode1,email1,other1,msn1,filepath,resume,ly,fa)
values(@cnusername,@sex,@name,@age,@zzmm,@jg,@sfz,@school,@profession,@sstel,@hometel,@address,@postcode,@email,@other,@msn,
@name1,@sex1,@age1,@zzmm1,@jg1,@sfz1,@school1,@profession1,@sstel1,@hometel1,@address1,@postcode1,@email1,@other1,@msn1,@path,@resumes,@ly,@fa)set @s=@@identity
end
set @s=0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER zjxdtr_insert
ON dbo.PersionInfo
FOR INSERT
AS
declare @username varchar(1000)declare @name varchar(1000)
declare @sex  varchar(10)
declare @age  varchar(10)
declare @zzmm varchar(50)
declare @jg varchar(100)
declare @sfz varchar(100)
declare @school varchar(100)
declare @profession varchar(100)
declare @sstel varchar(50)
declare @hometel varchar(50)
declare @address varchar(100)
declare @postcode varchar(50)
declare @email varchar(50)
declare @other varchar(50)
declare @msn varchar(100)
--sencd
declare @name1 nvarchar(100)
declare @sex1 varchar(10)
declare @age1 varchar(10)
declare @zzmm1 varchar(50)
declare @jg1 varchar(100)
declare @sfz1 varchar(100)
declare @school1 varchar(100)
declare @profession1 varchar(100)
declare @sstel1 varchar(50)
declare @hometel1 varchar(50)
declare @address1 varchar(100)
declare @postcode1 varchar(50)
declare @email1 varchar(50)
declare @other1 varchar(50)
declare @msn1 varchar(100)
declare @path varchar(100)
declare @resumes varchar(500)
declare @ly varchar(500)
declare @fa varchar(500)declare @zjid varchar(20)
declare @num int
select @zjid=max(zjid) from persioninfo where zjid  like 'jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '%'
if @zjid is null
   set @zjid='jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '-01'
else
   begin
     set @num=cast(substring(@zjid,10,2) as int)
     set @num=@num + 1
     if @num<10
     set @zjid='jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '-0' + cast(@num as varchar(2))
     else
     set @zjid='jzxd' + substring(convert(varchar(10),getdate(),112),5,4) + '-' + cast(@num as varchar(2))
   end
select @username=cnusername from inserted
select @sex=sex from inserted
select @name=name from inserted
select @age=age from inserted
select  @zzmm=zzmm from inserted
select @jg=jg from inserted
select @sfz=sfz from  inserted
select  @school=school from inserted
select @profession=profession from inserted
select @sstel=sstel from inserted
select @hometel=hometel from inserted
select @address=address from inserted
select @postcode=postcode from inserted
select @email=email from inserted
select @other=other from inserted
select @msn=msn from inserted
--第二个表select @sex1=sex1 from inserted
select @name1=name1 from inserted
select @age1=age1 from inserted
select  @zzmm1=zzmm1 from inserted
select @jg1=jg1 from inserted
select @sfz1=sfz1 from  inserted
select  @school1=school1 from inserted
select @profession1=profession1 from inserted
select @sstel1=sstel1 from inserted
select @hometel1=hometel1 from inserted
select @address1=address1 from inserted
select @postcode1=postcode1 from inserted
select @email1=email1 from inserted
select @other1=other1 from inserted
select @msn1=msn1 from inserted
select @path=filepath from inserted
select @resumes=resume from inserted
select  @ly=ly from inserted
select @fa=fa from inserted
-- @name1=name1,@sex1=sex1,@age1=age1,@zzmm1=zzmm1,@jg1=jg1,@sfz1=sfz1,@school1=school1,@profession1=profession1,@sstel1=sstel1,@hometel1=hometel1,@address1,@postcode1=postcode1,@email1=email1,@other1=other1,@msn1=msn1,@path=path,@resumes=resume,@ly=ly,@fa=farollback
--insert into persioninfo(zjid,cnusername) values(@zjid,@username)insert  persionInfo (zjid,cnusername,[name],
sex,age,zzmm,jg,sfz,school,profession,
sstel,hometel,address,postcode,email,other,msn,
name1,sex1,age1,zzmm1,jg1,sfz1,school1,profession1,sstel1,hometel1,address1,postcode1,email1,other1,msn1,filepath,resume,ly,fa)
select @zjid,@username,@sex,@name,@age,@zzmm,@jg,@sfz,@school,@profession,@sstel,@hometel,@address,@postcode,@email,@other,@msn,
@name1,@sex1,@age1,@zzmm1,@jg1,@sfz1,@school1,@profession1,@sstel1,@hometel1,@address1,@postcode1,@email1,@other1,@msn1,@path,@resumes,@ly,@fa

调试易

根据ID生成字符串？

解决方案 »