一个与触发器更新有关的问题.

[kfcn]

我的表有这么几列.
name num
pc 13
pc 14
pc 78
pd 99
pd 90
我现在增加一列(id),
用触发器更新得结果如下:

id name num
pc001 pc 13
pc002 pc 14
pc003 pc 78
pd001 pd 99
pd002 pd 90
.....
各路高手谁可以帮我吗?

[kfcn]

现在问题是我不知道如何写在这个触发器.
或是有其它什么方法,
先谢谢兄弟姐妹了..

[qingbai]

to kfcn:
1.按mjhnet(天涯沦落人)的解答你的问题解决了吗？
2.id列是想作为主键还是仅仅显示？
to mjhnet(天涯沦落人):
请教：
1.您的解答在机器上通过吗？正确吗？
2.对新插入的数据怎么办？
3.当修改了name列的值后怎么办？
4.若kfcn需要将id作为主键时怎么办？
5.每当插入新行或更新name列时，是否都要把您提供的语句执行一遍？

[qingbai]

to mjhnet(天涯沦落人):
6.当此表的数据量增大时，比如10行，100万行时，每次插入新行或更新name列时，都要把您提供的语句执行一遍合适吗？

[qingbai]

--请逐批执行-即一个GO一个GO的选中执行
--创建表
create table your_table(
[name] char(2),
num int
)
go
--插入数据
insert into your_table([name],num) values('pc',13)
insert into your_table([name],num) values('pc',14)
insert into your_table([name],num) values('pc',78)
insert into your_table([name],num) values('pd',99)
insert into your_table([name],num) values('pd',90)
go

--增加一列[id]
alter table your_table
add [id] char(5)
go

--在现有数据中为新加的列[id]添充值
declare @i int,@name char(2)
update your_table
set @i=case [name]
when @name then @i+1
else 1
end,
@name=[name],
[id]=[name]+case
when @i between 1 and 9 then '00'+cast(@i as char(1))
when @i between 10 and 99 then '0'+cast(@i as char(2))
else cast(@i as char(3))
end
go

select * from your_table

--为列[id]添加一个默认值
ALTER TABLE dbo.your_table ADD CONSTRAINT
DF_your_table_id DEFAULT ' ' FOR [id]
go

--为插入动作添加触发器
CREATE TRIGGER [trig_id_i] ON dbo.your_table
FOR INSERT
AS
DECLARE
@id CHAR(5),
@name CHAR(2),
@i smallint

SELECT @name=[name] FROM inserted
SELECT @i=count(*)
FROM your_table
WHERE [name]=@name

SET @id=@name+case
when @i between 1 and 9 then '00'+cast(@i as char(1))
when @i between 10 and 99 then '0'+cast(@i as char(2))
else cast(@i as char(3))
end

UPDATE your_table
SET [id]=@id
FROM your_table y JOIN inserted i ON y.[name]=i.[name] and y.num=i.num
go

--为修改动作添加触发器
CREATE TRIGGER [trig_id_u] ON dbo.your_table
FOR UPDATE
AS
IF UPDATE([name])
BEGIN
DECLARE
@id CHAR(5),
@name CHAR(2),
@i smallint

SELECT @name=[name] FROM inserted
SELECT @i=count(*)
FROM your_table
WHERE [name]=@name

SET @id=@name+case
when @i between 1 and 9 then '00'+cast(@i as char(1))
when @i between 10 and 99 then '0'+cast(@i as char(2))
else cast(@i as char(3))
end

UPDATE your_table
SET [id]=@id
FROM your_table y JOIN inserted i ON y.[name]=i.[name] and y.num=i.num
END
GO

insert into your_table([name],num) values('pc',79)
insert into your_table([name],num) values('pd',91)
go

select * from your_table order by [id]

[mjhnet]

一句话搞定

update 表 set id = cast((select count(*) from 表 别名 where 别名.name = 表.name and 别名.num <= 别名.num) as char(3))

至于加前缀的事情完全可以在取出显示的时候再作，如下

select rtrim(name) + replicate('0',3-len(id))+id, name, num from 表

[friendliu]

用函数或存储过程实现好些

[leimin]

create table t(
[name] char(2),
num int
)
go
--插入数据
insert into t([name],num) values('pc',13)
insert into t([name],num) values('pc',14)
insert into t([name],num) values('pc',78)
insert into t([name],num) values('pd',99)
insert into t([name],num) values('pd',90)
go
alter table t add [id] varchar(5) null
go
update t set [id]=cast((select Count(*) from t a
where a.[name]=t.[name] and a.num<=t.num) as varchar(5))
update t set [id]= rtrim([name]) + replicate('0',3-len(id))+id

go
select * from T

[kfcn]

也可以设为主键的啊.

[kfcn]

create table fixed(id varchar(20),category varchar(10),yes int identity(1,1))
go
create trigger fixed_901 on fixed for insert
as
update fixed set fixed.id=cast((select count(*) from fixed f
where fixed.category=f.category and f.yes<=fixed.yes) as char(3))
update fixed set id=rtrim(category)+replicate('0',3-len(id))+id

insert fixed(category) values ('prl')
insert fixed(category) values ('prl')
insert fixed(category) values ('prl')
insert fixed(category) values ('pc')
insert fixed(category) values ('prl')
insert fixed(category) values ('pc')
insert fixed(category) values ('prl')
insert fixed(category) values ('sw')
insert fixed(category) values ('sw')
insert fixed(category) values ('prl')
insert fixed(category) values ('prl')
insert fixed(category) values ('prl')
insert fixed(category) values ('pc')
insert fixed(category) values ('prl')
insert fixed(category) values ('pc')
insert fixed(category) values ('prl')
insert fixed(category) values ('sw')
insert fixed(category) values ('sw')

go

select * from fixed order by id
id category yes
pc001 pc 4
pc002 pc 6
pc003 pc 13
pc004 pc 15
prl001 prl 1
prl002 prl 2
prl003 prl 3
prl004 prl 5
prl005 prl 7
prl006 prl 10
prl007 prl 11
prl008 prl 12
prl009 prl 14
prl010 prl 16
sw001 sw 8
sw002 sw 9
sw003 sw 17
sw004 sw 18

[kfcn]

黄山光明顶做得很好..