#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    Trigger before DROP or ALTER


    Hi,
    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?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    117
    Rep Power
    9
    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....
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    Hi,
    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.

IMN logo majestic logo threadwatch logo seochat tools logo