alter trigger createscore
on examlist
for insert
as
declare @examtime varchar(10),@examlistid int
begin
if @@rowcount!=0
begin
select @examtime=考试时间,@examlistid=ExamlistID from examlist
create table @examtime
(StudentID varchar(20),
@examlistid int,
constraint FK_StudentID foreign key(StudentID)references student(StudentID)
)
end
end
这样不行,把@examtime改成['+@examtiime+']也不行,在前面加上exec也不行,请高手指教,研究好几天了......
on examlist
for insert
as
declare @examtime varchar(10),@examlistid int
begin
if @@rowcount!=0
begin
select @examtime=考试时间,@examlistid=ExamlistID from examlist
create table @examtime
(StudentID varchar(20),
@examlistid int,
constraint FK_StudentID foreign key(StudentID)references student(StudentID)
)
end
end
这样不行,把@examtime改成['+@examtiime+']也不行,在前面加上exec也不行,请高手指教,研究好几天了......
set @sql='create table '+@examtime+'
(StudentID varchar(20),'+
@examlistid+' int,'+'
constraint FK_StudentID foreign key(StudentID)references student(StudentID)'
execute sp_executesql sql
用exec 执行
on examlist
for insert
as
declare @examtime varchar(10),@examlistid int
declare @sqlstr varchar(1000)
begin
if @@rowcount!=0
begin
select @examtime=考试时间,@examlistid=ExamlistID from examlist
set @sqlstr = 'create table ' + @examtime +
'(StudentID varchar(20),
@examlistid int,
constraint FK_StudentID foreign key(StudentID)references student(StudentID)
)'
exec (@sqlstr)
end
end
用exec 执行语句
(studentID varchar(20),'数据类型转换成int 时失败
你这个查出来的,@examlistid是数字吧
不能用数字做数据库的字段
declare @examtime datetime,@examlistid varchar(30)
select @examtime=t_date,@examlistid=t_id from test
select @sqlStr = 'create table ['+convert(varchar(30),@examtime)+']
(StudentID varchar(30) references publisher(pu_Id),
['+@examlistid+'] varchar(30)
)'
exec(@sqlStr)
ON ExamList
FOR INSERT
AS
BEGIN
DECLARE @ExamTime VARCHAR(10),@ExamListId INT
SELECT @ExamTime=CONVERT(VARCHAR(10),考试时间,120),@ExamListId=ExamListId FROM ExamList
EXEC('CREATE TABLE [' + @ExamTime + '](StudentID VARCHAR(20),[' + RTRIM(@ExamListId) + '] INT,CONSTRAIN FK_StudentID FROEIGN KEY(StudentID) REFERENCES Student(StudentID))')
END
随手敲的,可能有手误因为是 FOR INSERT的触发器,所以不需要检测 INSERTED表是否有值.
set @sql='create table '+@examtime+'
(StudentID varchar(20),'+
cast(@examlistid as nvarchar(4000)+' int,'+'
constraint FK_StudentID foreign key(StudentID)references student(StudentID)'
execute sp_executesql sql
'+@examtime+'
转换成:
'+ltrim(rtrim(str(@examtime)))+'