如何使用触发器实现动态新增列？

create table ta(number int)

create trigger test_tr on ta

for insert

as

begin

DECLARE @number int,@sql varchar(4000),@sql1 varchar(4000),@i int

DECLARE roy CURSOR

FOR SELECT * from inserted

OPEN roy

FETCH next FROM roy into @number

WHILE @@FETCH_STATUS = 0

begin

begin

if exists(select 1 from sysobjects where name='tb' and xtype='U')

begin

select @i=max(colid)+1 from syscolumns where id=object_id('tb')

set @sql=N'alter table tb add C'+cast(@i as varchar)+' int null'

set @sql1=N'update tb set C'+cast(@i as varchar)+'='+cast(@number as varchar)

--print @sql

--print @sql1

exec(@sql)

exec(@sql1)

end

else

begin

set @sql=N'create table tb(C1 int)'

set @sql1=N' insert tb select C1='+cast(@number as varchar)

exec(@sql)

exec(@sql1)

end

end

FETCH NEXT FROM roy INTO @number

end

CLOSE roy

DEALLOCATE roy

end

测试：

insert ta

select 10 union all

select 13 union all

select 18 union all

select 26

查询：

select * from tb

C1 C2 C3 C4

----------- ----------- ----------- -----------

10 13 18 26

（所影响的行数为 1 行）

--drop table ta,tb

本文地址：http://www.45fan.com/a/question/70536.html