/*--------------建表--------------*/
[create] table a
(id int identity(1,1),xm varchar(20),pj varchar(50),dj varchar(50))
go
/*---------------函数来计算表中字符个数--------------*/
create function f_dj(@col varchar(2000))
returns varchar(2000)
as
begin
declare @sql varchar(2000)
declare @t table (id int identity(1,1),col varchar(2))
set @sql=''
while(len(@col)>0)
begin
insert @t values (left(@col,1))
set @col=stuff(@col,1,1,'')
end
select @sql=@sql+cast(c as varchar)+col from
(
select id,col,c=(select count(1) from @t where col=a.col) from @t a
) a where (select count(*) from
(
select id,col,c=(select count(1) from @t where col=a.col) from @t a
) b where a.col=b.col and a.c=b.c and a.id>b.id)<1
return(@sql)
end
go
/*-----------------------触发器来实现更新---------------------*/
create trigger tr_a
on a
for insert,update
as
set xact_abort on
begin tran
update a set dj=dbo.f_dj(b.pj)
from inserted b,a
where a.id=b.id
commit tran
go
/*--------------测试区---------------*/
insert into a values ('张三','ABCDE',null)
select * from a
update a set pj='BBDREF' where id=1
select * from a
insert into a values ('李四','TFDKD',null)
insert into a values ('王五','FDKKDFM',null)
insert into a values ('唐六','DKBVHKD',null)
select * from a
/*----------------删除环境-----------------*/
drop trigger tr_a
drop function dbo.f_dj
drop table a
/*----------------执行情况-----------------*/
id xm pj dj
----------- -------------------- -------------------------------------------------- --------------------------------------------------
1 张三 ABCDE 1A1B1C1D1E
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
id xm pj dj
----------- -------------------- -------------------------------------------------- --------------------------------------------------
1 张三 BBDREF 2B1D1R1E1F