SQL Server View Trigger Definition

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:

SQL Server View Trigger Definition - querying system view

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:

  1. First, in Object Explorer, connect to the database and expand that instance.
  2. Second, expand the database and table which contains the trigger that you want to view the definition.
  3. 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.
SQL Server View Trigger Definition

In this tutorial, you have learned various ways to view the definition of a trigger.

Was this tutorial helpful?