我的表有这么几列.
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
.....
各路高手谁可以帮我吗?
...全文
3110打赏收藏
一个与触发器更新有关的问题.
我的表有这么几列. 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 ..... 各路高手谁可以帮我吗?
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
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
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一个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