SQL Server Performance

How to script triggers?

Discussion in 'SQL Server 2005 General Developer Questions' started by pcsql, Jun 22, 2006.

  1. pcsql New Member

    I want to do the followings in SQL Server 2000 and SQL Server 2005:

    1. Script existing triggers from all the tables in a database using T-SQL and save the scripts in a permanent table in SQL Server
    2. Delete the existing triggers from all the tables in the database using T-SQL
    3. Create triggers using the scripts saved the permanent table


  2. Luis Martin Moderator

    Why do you don't want to use EM for that?

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  3. pcsql New Member

    Hi Luis,

    I need this to be done programmatically in T-SQL.


  4. FrankKalis Moderator

    I see you already had the same idea:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15117

    So it needs to be in T-SQL?
    Well, you first need to have the names of the triggers. After that you can use a loop to pipe the result of sp_helptext into a table. At that point you have the source code of the triggers. Now you can drop them and do your fancy stuff. To recreate the trigger you can use another loop along with sp_executesql. I think this should work. At least as long as your trigger code fits into a VARCHAR(8000).

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
  5. FrankKalis Moderator

  6. Madhivanan Moderator

    Why do you want to do this programmatically?


    Failing to plan is Planning to fail
  7. pcsql New Member

    Hi Madhivanan,

    I want to remove the triggers and then do some updating (both data and structure) to the tables and then add them back. From my own testing,disabling the triggers are 2-3 times slower than removing the trigger. I think the ratio depends on # of records and other factors such as data storage.


Share This Page