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

sql sql-server-2008 triggers
Grasshopper picture Grasshopper · Nov 2, 2012 · Viewed 77.3k times · Source

I'd like to generate an SQL Script that contains the SQL to create all of the triggers that exist in our database. The triggers were added directly via the SSMS query pane so, there is currently no source other than the trigger on the database itself.

I have already tried the method where you right-click the database, select Tasks->Generate Scripts and used the "Script Entire Database and All Objects" option. While this does create a SQL script for the tables and constraints, it does not generate SQL for the triggers.

I also understand that I can right click on each trigger in the database and select the Generate SQL Script option but, there is currently 46 tables under audit (For Insert, Update, and Delete).

Rather manually generate an insert, update, and delete trigger script for each of the 46 tables, is there an easier way to do this? Or, should I start clicking, copying, and pasting?

Answer

Kapil Khandelwal picture Kapil Khandelwal · Nov 2, 2012

Database-> Tasks-> Generate Scripts -> Next -> Next

On Choose Script Options UI, under Table/View Options Heading, set Script Triggers to True.

enter image description here

enter image description here

Related questions

How can I edit values of an INSERT in a trigger on SQL Server?

I have the table Tb ID | Name | Desc ------------------------- 1 | Sample | sample desc I want to create a trigger on INSERT that will change the value of the inserting Desc, for example: INSERT INTO Tb(Name, Desc) VALUES ('x', 'y') Will …

Read More
How to use update trigger to update another table?

I am new to triggers and want to create a trigger on an update of a column and update another table with that value. I have table1 with a year column and if the application updates that year column I …

Read More
How do I prevent a database trigger from recursing?

I've got the following trigger on a table for a SQL Server 2008 database. It's recursing, so I need to stop it. After I insert or update a record, I'm trying to simply update a single field on that table. Here's …

Read More