Summary: in this tutorial, you will learn various ways to view SQL Server trigger definition.
Getting trigger definition by querying from a system view
You can get the definition of a trigger by querying data against the sys.sql_modules
view:
SELECT
definition
FROM
sys.sql_modules
WHERE
object_id = OBJECT_ID('sales.trg_members_delete');
Code language: SQL (Structured Query Language) (sql)
Here is the output:
In this query, you pass the name of the trigger which you want to get the definition to the OBJECT_ID()
function in the WHERE
clause.
Getting trigger definition using OBJECT_DEFINITION function
You can get the definition of a trigger using the OBJECT_DEFINITION
function as follows:
SELECT
OBJECT_DEFINITION (
OBJECT_ID(
'sales.trg_members_delete'
)
) AS trigger_definition;
Code language: SQL (Structured Query Language) (sql)
In this query, you pass the trigger name to the OBJECT_ID function to get the ID of the trigger. Then, you use the OBJECT_DEFINITION()
function to get the Transact-SQL source text of the definition of a trigger based on its ID.
Getting trigger definition using sp_helptext stored procedure
The simplest way to get the definition of a trigger is to use the sp_helptext stored procedure as follows:
EXEC sp_helptext 'sales.trg_members_delete' ;
Code language: SQL (Structured Query Language) (sql)
The sp_helptext
stored procedure returns the definition used to create an object, in this case, a trigger.
Getting trigger definition using SSMS
To view the definition of a DML trigger:
- First, in Object Explorer, connect to the database and expand that instance.
- Second, expand the database and table which contains the trigger that you want to view the definition.
- Third, expand Triggers, right-click the trigger you want to view the definition, and then click Modify. The trigger definition appears in the query window.
In this tutorial, you have learned various ways to view the definition of a trigger.