0

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

apxcode
  • 7,696
  • 7
  • 30
  • 41
user1862341
  • 171
  • 1
  • 9
  • 1
    "three or more columns storing image file path" seems to be the thing causing the root problem. Have you considered restructuring into first normal form? You could unpivot `column1,column2,column3` though to do it all in one `insert` statement. – Martin Smith Nov 29 '12 at 15:10
  • in most cases,third normal form is the base line for normalize ,so i can't persuade myself to break rules,i think it may cause more problem than it solved and for some reason i want to do so, but unfortunately the project almost complete ,if i change the table structure,will cause more change to project,and that will be another big problem still,thank you all the same for you kindness – user1862341 Nov 30 '12 at 01:09
  • I'm saying that it seems likely that you are **not** even at first normal form at the moment as you appear to have a repeating group. – Martin Smith Nov 30 '12 at 08:16

1 Answers1

2

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.

Ben
  • 34,935
  • 6
  • 74
  • 113
  • `INSERT INTO OnDeleteFile (filePath) SELECT image_path FROM T UNPIVOT (image_path FOR column_name IN (column1, column2, column3) )AS U;` would only do 1 scan of `deleted` – Martin Smith Nov 29 '12 at 15:48
  • @MartinSmith, true, but "scan" is a heavy word when there will by typically one row only. – Ben Nov 29 '12 at 16:35
  • @Ben thank you very much , your simple answer let me realized that it is the work i should do ,although it is repeated, it is the better performance solution ,that is my obligation and that is my job – user1862341 Nov 30 '12 at 01:19
  • @MartinSmith solution is also fine! – Ben Nov 30 '12 at 10:45