0

Is there a way to update/alter all triggers using Looping T-SQL or a C# Code ?. I need it too update all the triggers in my database because the RAISERROR command has changed in sql server 2012.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
seyren windsor
  • 79
  • 1
  • 9
  • 1
    Did you even try `ALTER TRIGGER`? – LittleBobbyTables - Au Revoir Apr 08 '13 at 01:10
  • @LittleBobbyTables im not talking about manually create alter script for each of the triggers. What i want is to create a Loop through each of my triggers let's say I have 500 triggers and Replace a specified syntax/command to a new one – seyren windsor Apr 08 '13 at 01:28
  • This is not a duplicate of the proposed duplicate [SO 8607262](http://stackoverflow.com/q/8607262). That's about a single ALTER TRIGGER; this is about automating many of them. – Jonathan Leffler Apr 15 '13 at 03:06

2 Answers2

3

You can script out all the triggers into a text file. See:

How to Generate Scripts For All Triggers in Database Using Microsoft SQL Server Management Studio

Then, you can use a text editor to replace the code you need to replace. Then, you can drop your triggers and recreate them. I don't know if your referring to a production database, or one that you are doing development on. I am assuming from your question that you are prepared to make these major changes to your database. Make sure you back up your database first, in any event.

Community
  • 1
  • 1
Michael Harmon
  • 746
  • 5
  • 10
  • Thanks for the link. I'll try to read the link you post. And also I am willing to recreate them because I dont have a choice either. – seyren windsor Apr 08 '13 at 01:45
  • SQL encounter an error, OutOfMemoryException by the way I have 100+ tables and most of these tables have at least 3 triggers ( delete, insert, update ). Is there any other way to script it ? – seyren windsor Apr 08 '13 at 03:24
  • Not sure why you got the error. In the script, is each create statement separated with a "GO" delimiter, or is it just one CREATE TRIGGER after another? Try separating each CREATE TRIGGER with a GO statement at the end of the trigger. This will separate each one into a separate batch in SSMS. – Michael Harmon Apr 10 '13 at 00:02
2

You can get all trigger using 

SELECT [name] FROM [sys].[triggers]

and loop through each trigger and execute 

EXEC sp_helptext 'TriggerName'

so you will have the create statement with it...

SAM
  • 825
  • 1
  • 7
  • 15
  • thanks for this. Maybe this is the only way, to get the create script and tweak the RAISERROR syntax to the new one. THANKS!! – seyren windsor Apr 08 '13 at 04:12