我在au_Content表里有两个字段分别是SubTitle和Author
subtitle的值已经定义为RC002000101了,我本想每添加一条内容后,生成Subtitle加年份和月份再加流水号001、002、003等形成RC002000101201105001,RC002000101201105002,RC002000101201105003……可现在添加内容后一直是RC002000101201105000 后面的三位数不变 我不知道哪里出错了 恳请大家帮帮忙!!!CREATE trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin
declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = count(*) from au_Content where SubTitle like @col + convert(varchar(6),getdate(),112)+'%'
set @number='00'+@number;
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id
end
end
end
subtitle的值已经定义为RC002000101了,我本想每添加一条内容后,生成Subtitle加年份和月份再加流水号001、002、003等形成RC002000101201105001,RC002000101201105002,RC002000101201105003……可现在添加内容后一直是RC002000101201105000 后面的三位数不变 我不知道哪里出错了 恳请大家帮帮忙!!!CREATE trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin
declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = count(*) from au_Content where SubTitle like @col + convert(varchar(6),getdate(),112)+'%'
set @number='00'+@number;
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id
end
end
end
问题就出现了
select @col = SubTitle from inserted
select @id = ID from inserted
subtitle的值已经定义为RC002000101了,我就是想实现:每添加一条内容,在Author的值成为:Subtitle的值加年份、月份再加流水号001、002、003等形成RC002000101201105001,RC002000101201105002,RC002000101201105003……
大神们有什么好的办法帮帮我吧,我不懂这个但又必须解决这个问题!恳求你们了!
alter trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = case when count(*)=0 then 1 else count(*) end from au_Content where SubTitle like @col + convert(varchar(6),getdate(),112)+'%'set @number='00'+@number;
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id end
end
end
alter trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin
declare @sql as varchar(2000)
declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = count(*)+1 from au_Content where Author like @col + convert(varchar(6),getdate(),112)+'%'
set @sql='select count(*) from au_Content where SubTitle like '+ltrim(@col) + convert(varchar(6),getdate(),112)+'%'
print @sql
set @number='00'+@number;
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id end
end
end
非要触发器的话吧这句话
select @number = count(*)+1 from au_Content where Author like @col + convert(varchar(6),getdate(),112)+'%'改成
select @number = max(cast(right(SubTitle,3) as int))+1 from au_Content where Author like @col + convert(varchar(6),getdate(),112)+'%'
alter trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = cast(right(max(Author),3) as int)+1 from au_Content where Author like @col + convert(varchar(6),getdate(),112)+'%'set @number=right('00'+@number,3);
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id
end
end
end
returns varchar(20)
as
begin
declare @maxnumber int
declare @number char(16);
select @maxnumber=isnull(right(convert(varchar(12),max(number)),4),0) from tb where
substring(number,1,8)=convert(char(8),getdate(),112)
if @maxnumber is null
set @maxnumber=0;
set @maxnumber=@maxnumber+1;
select @number=convert(char(8),getdate(),112)+right('00000'+cast(@maxnumber as varchar(4)),4) return @number
end
这是我创建的一个函数.可以实现.你参考下.
alter trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = isnull(cast(right(max(Author),3) as int),0)+1 from au_Content where Author like @col + convert(varchar(6),getdate(),112)+'%'set @number=right('00'+@number,3);
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id
end
end
end
RC002000123201105008 已审核 编辑 评论(0)
RC002000123201105006 已审核 编辑 评论(0)
/****** 对象: Table [dbo].[au_Content] 脚本日期: 06/10/2011 12:03:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[au_Content](
[SubTitle] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Author] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
go
--2.建立触发器
create trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = isnull(cast(right(max(Author),3) as int),0)+1 from au_Content where Author like @col + convert(varchar(6),getdate(),112)+'%'set @number=right('00'+@number,3);
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id
end
end
end
go
--3.反复执行下面的插入语句,观察显示结果
insert dbo.au_Content(SubTitle) select 'RC002000101'
select * from dbo.au_Content
用FOR 触发器,是先把数据插入,再对数据进行更新,而用 INSTEAD OF 触发器,是把数据进行修改后一次性插入,效率高低你能知道的.
--我试下来都没错的,这是我所有的脚本,包括建表和建立触发器--1.建表
/****** 对象: Table [dbo].[au_Content] 脚本日期: 06/10/2011 12:03:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[au_Content](
[SubTitle] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Author] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
go
--2.建立触发器
create trigger [dbo].[Trigger2]
ON [dbo].[au_Content]
FOR Insert
AS
begin declare @col as varchar(20)
declare @id as int select @col = SubTitle from inserted
select @id = ID from inserted
if left(@col,2) = 'RC'
begin
if len(@col)=11
begin
declare @number as varchar(20)
select @number = isnull(cast(right(max(Author),3) as int),0)+1 from au_Content where Author like @col + convert(varchar(6),getdate(),112)+'%'set @number=right('00'+@number,3);
update dbo.au_Content set Author = @col+ convert(varchar(6),getdate(),112) + right(@number,3) where ID=@id
end
end
end
go
--3.反复执行下面的插入语句,观察显示结果
insert dbo.au_Content(SubTitle) select 'RC002000101' union all select 'RC002000102'
select * from dbo.au_Content
/*
SubTitle Author id
-------------------------------------------------- -------------------- -----------
RC002000101 RC002000101201105001 1
RC002000102 NULL 2(2 行受影响)
*/
go
drop table au_content
select * from dbo.au_Content
对的,只适合一次性插入一条的 ,下面这样一次性插入两条 select 'RC002000101' union all select 'RC002000102',第二条肯定取不到前一条插入后的后的最大值
select * from tb order by left(subtitle,9),dt