建一个触发器 各位高手帮我看下
create Trigger insert_tri
on 表名
for insert
as
declare @aa varchar(50)
@aa = select 编号 from inserted
alter table 表名 add @aa 列数据类型 默认值
这样为什么不可以
当在A表中插入一行时 就在B表中插入一个列 列名字是A表的编号
要实现的功能是
原来B表
1列 2列 3列
触发后
B表成为
1列 2列 3列 4列（新增列）
先谢谢了
[解决办法]
这样试试
create Trigger insert_tri
on 表名
for insert
as
declare @aa varchar(50)
declare @sql varchar(1000)
set @sql= ' '
declare cur_tmp cursor for
select 编号 from inserted
open cur_tmp
fetch next from cur_tmp into @aa
while @@fetch_status=0
begin
select @sql= 'alter table 表名 add '+@aa+ ' 列数据类型 默认值 '
exec(@sql)
fetch next from cur_tmp into @aa
end
close cur_tmp
deallocate cur_tmp
[解决办法]
create Trigger insert_tri
on 表名
for insert
as
declare @aa varchar(50)
select @aa = 编号 from inserted
alter table 表名 add @aa 列数据类型 默认值
[解决办法]
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
