createtriggerTR_CTableToSITable_IUoncourseTableforinsert,updateasbegindeclare@t_IDvarchar(20),@t_namevarchar(20),@t_tvarchar(20),@t_CNvarchar(20),@t_Mfloatselect@t_ID=ID,...
create trigger TR_CTableToSITable_IU on courseTable for insert,update as
begin
declare @t_ID varchar(20),@t_name varchar(20),@t_t varchar(20),@t_CN varchar(20),@t_M float
select @t_ID=ID,@t_name =name,@t_t=time,@t_CN=course,@t_M = Money from inserted
if EXISTS (select * from syscolumns where id=object_id('showInforTable')and name=@t_CN)
update showInforTable set ['+@t_CN+'] = @t_M where 工号 = @t_ID and 时间 = @t_t
else
ALTER TABLE [showInforTable] ADD ['+@t_CN+'] float NULL
update showInforTable set ['+@t_CN+'] = @t_M where 工号 = @t_ID and 时间 = @t_t
if @@error<>0GOTO error
return
error: raiserror('数据录入showInforTable失败',16,1)
end
大概意思是想在inserted表中取出数据course作为showInforTable表中的列名,如果列名存在,就更新showInforTable表中该列的数据,如果不存在就增加列名,这里SQL提示是列名 ''+@t_CN+'' 无效。
求大神指导啊~~~
展开