In my table there are three or more columns stored image file path, so I need to store the image file path to an specify table 'OnDeleteFile' when the data is going to delete,then the program to delete file in web server in task methord from this specify table but there are many tables like this satuation, i don't want to write every times in every triggers to insert each column into table OnDeleteFile like this:
insert into OnDeleteFile(filePath) select column1 from deleted,
insert into OnDeleteFile(filePath) select column2 from deleted,
insert into OnDeleteFile(filePath) select column3 from deleted
there are so many tables and so many columns,so i hope i can do this by loop
tsql like this:
declare mycursor cursor fast_forward
for select columnName from #temptable where isContent=0
open mycursor
fetch next from mycursor into @column
while @@fetch_status = 0
begin
select @sqlstr='insert into OnDeleteFile(fileType,filePath) select fileType=''image'',@columnName from deleted where @columnName is not null and @columnName <> '''' '
exec sp_executesql
@stat=@sqlstr,
@params=N'@columnName as varchar(255)',
@columnName=@column
fetch next from mycurosr into @column
end
And it run error :object name 'deleted' is invalid
I try this way :
declare mycursor cursor fast_forward
for select columnName from #temptable where isContent=0
open mycursor
fetch next from mycursor into @column
while @@fetch_status = 0
begin
exec('insert into OnDeleteFile(fileType,filePath) select fileType=''image'','+ @column +' from deleted where '+ @column + ' is not null and '+ @column + ' <> '''' ')
end
fail again
I think the problem is the scope of deleted table can be used ,the deleted table can't be access in proc sp_executesql
so only i can do is to create a temp table to walk around this problem , but if so ,for this simple goal ,i used trigger, cursor , temptable and sub procedure call , i really care about the cost of compute
if anyone have some better solutions?
PS: I'm very sorry for my pool English and my pool question
PPS:i know there are something wrong about the needs to do this complex thing ,if the programmer in our team be willing to delete file at the same time when delete the data ,i will no need to do this , but they won't , so i just try in my scale
No, don't do that, do it the way you don't want to do.
insert into OnDeleteFile(filePath) select column1 from deleted,
insert into OnDeleteFile(filePath) select column2 from deleted,
insert into OnDeleteFile(filePath) select column3 from deleted
It's a MUCH better idea.