Question

关于SQL利用触发器删除表的问题

create trigger DelStuScore
on exam_stuScore
for delete
as
begin
drop table deleted.paperdb_name
end

我想创建一个触发器,当删除exam_stuScore表中数据时,就同时删除表名为被删数据中paperdn_name的表,可是我用deleted.paperdb_name想取出被删的数据,好像取不出来,就是出错,不存在名'deleted.paperdb_name'的表!
请高手赐教!

Answer 1

不用那么麻烦
触发器里的内容直接如下

insert into delclasslist(StuID,Student,fid,StuClass,Teacher,cid)
select stuid,student,fid,stuclass,teacher,id from deleted
就可以了，你懂的。希望能有所帮助！欢迎到IT实验室,天天软件测试网看看MFM。365testing.com。MFM

Answer 2

不用那么麻烦
触发器里的内容直接如下

insert into delclasslist(StuID,Student,fid,StuClass,Teacher,cid)
select stuid,student,fid,stuclass,teacher,id from deleted
就可以了，你懂的。欢迎共同讨论！欢迎到CSDN,365testing,测评网看看BJQ。365testing.com。BJQ

Answer 3

不用那么麻烦
触发器里的内容直接如下

insert into delclasslist(StuID,Student,fid,StuClass,Teacher,cid)
select stuid,student,fid,stuclass,teacher,id from deleted
就可以了，你懂的。欢迎共同讨论！欢迎到it168,365testing看看OGU。365testing.com。OGU

Answer 4

begin
declear @table_name nvarchar(50)
set @table_name = select paperdn_name from deleted
exec 'drop table '+ @table_name //因为表名是字符串，不能直接执行，所以拼接SQL
end

追问

create trigger DelStuScore
on exam_stuScore
for delete
as
begin
declare @table_name nvarchar(50)
set @table_name = select paperdb_name from deleted
exec 'drop table' + @table_name
end
关键字 'select' 附近有语法错误
'drop table' 附近有语法错误啊
按你这样写出了这两个错误啊，咋办好？我对sql不熟悉>_<、

Answer 5

create trigger DelStuScore
on exam_stuScore
for delete
as
begin
declare @sql nvarchar(4000)
set @sql = ''
select @sql = @sql + 'drop table ' + paperdb_name + ';' from deleted
execute(@sql)
end

追问

万分感谢终于行了，有一个地方不是很明白，请问一下：
select @sql = @sql + 'drop table ' + paperdb_name + ';' from deleted
为什么from deleted要放到 ；号后面呢？而不是
select @sql = @sql + 'drop table ' + deleted.paperdb_name ？