45fan.com - 路饭网

搜索: 您的位置主页 > 网络频道 > 阅读资讯:如何使用触发器实现动态新增列?

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

2016-09-01 06:38:34 来源:www.45fan.com 【

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

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
Tags: 实现 动态 触发器
编辑:路饭网
关于我们 | 联系我们 | 友情链接 | 网站地图 | Sitemap | App | 返回顶部