February 3rd, 2013, 04:28 AM
Trigger before DROP or ALTER
I have the following question:
I have an update skript which I cannot modify but which is very slow. To speed it up i created indexes on some of the tables.
The problem is that the script at some point will try to drop a column on which it has an index on. So i tried to find a solution for this by creating a trigger which will execute before a DROP statement but as I found out this is not possible. Also an trigger for an alter_table is not possible as it will only be executed after an succesful alter table. So how could I achieve to drop the index before the alter table drop column statement is executed? I know the exact statement, so could I somehow get an rewrite of the statement that is run?
February 5th, 2013, 09:16 AM
Can you explain why you need to drop table column and recreate index during update? I think it is not right.
Drop index, alter table, create index will be always slow... and not looks right in update....
February 6th, 2013, 06:41 AM
the drop of the column is part of the update-procedure of an CRM-Application. The statements itselfs are from an .dll where I can`t get the sources for. So the dll is used to update the db, the only thing I can see is what the SQL-Profiler shows me. This way I found out how many indexes I could use to speed up the process. The creation and the drop of the indexes takes abiut 3min.
As one step of the update procedure takes about 20h without custom indexes (and there about 20 steps in total) I can`t let the profiler run all the time and I also don`t want to rewrite the whole updateprocedure just with the help of the SQL-Profiler.
It would not be a problem if the statement was an insert update or delete as I could use an "instead of" trigger. So I am seraching for something like an rewrite rule for actual queries or for something like an "instead of"-Trigger for an Alter Table statement.