SQL Server Performance Forum – Threads Archive

How to script triggers?

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
Thanks, Peter

Why do you don’t want to use EM for that? Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi Luis, I need this to be done programmatically in T-SQL.
Thanks, Peter

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
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Correction. I think you are limited to Unicode with sp_executesql. Therefore your trigger code must fit into a NVARCHAR(4000).
See if this helks:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15123 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Why do you want to do this programmatically? Madhivanan Failing to plan is Planning to fail

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.
Peter

]]>