用触发器动态扩展表属性

有两张表
T_SpareTypeInfo
SpareID       Type       Amount       

T_SpareTypeTempInfo
SpareID


若往表T_SpareTypeInfo插入一条纪录
SpareID          Type         Amount 
001            InnerType        20.00
则T_SpareTypeTempInfo 中就插一个字段InnerType
表T_SpareTypeTempInfo信息:
SpareID     InnerType
001           20.00 


若再往表T_SpareTypeInfo插入一条纪录
SpareID          Type         Amount
001            OutType        10.00
则T_SpareTypeTempInfo 中就再插一个字段OutType
表T_SpareTypeTempInfo信息 :
SpareID     InnerType    OutType
001           20.00      10.00

若再往表T_SpareTypeInfo插入一条纪录
SpareID          Type         Amount
002            AllType         10.00
表T_SpareTypeTempInfo信息 :
SpareID     InnerType     OutType      AllType        
001           20.00       10.00        NULL
002           NULL        NULL        10.00
....

增加更多的SpareID，Type，Amount
表T_SpareTypeTempInfo信息会横向纵向增加 

解决方法：

create table T_SpareTypeInfo(
SpareID char(10),
Type char(10),
Amount char(10))

create table T_SpareTypeTempInfo(
SpareID char(10))

drop trigger tri

CREATE TRIGGER tri
ON T_SpareTypeInfo
FOR INSERT
as
declare @SpareID varchar(10)
declare @Type varchar(10)
declare @Amount varchar(10)
declare @sql varchar(1000)
select @SpareID=SpareID,@Type=Type,@Amount=Amount from inserted
set @sql='ALTER TABLE T_SpareTypeTempInfo ADD '+@Type+' VARCHAR(20)'
print @sql
exec(@sql)
if exists(select * from T_SpareTypeTempInfo where SpareID=@SpareID)
begin
set @sql='update T_SpareTypeTempInfo set '+@Type+'='''+@Amount+''' where SpareID='''+@SpareID+''''
print @sql
exec(@sql)
end
else
begin
set @sql='insert into T_SpareTypeTempInfo(SpareID,'+@Type+') values('''+@SpareID+''','''+@Amount+''');'
print @sql
exec(@sql)
end
GO

select * from T_SpareTypeInfo
select * from T_SpareTypeTempInfo
insert into T_SpareTypeInfo
values('002','AllType','10.00')