{"id":990,"date":"2018-10-21T11:03:32","date_gmt":"2018-10-21T04:03:32","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=990"},"modified":"2020-04-11T20:13:04","modified_gmt":"2020-04-11T13:13:04","slug":"sql-server-view-trigger-definition","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-triggers\/sql-server-view-trigger-definition\/","title":{"rendered":"SQL Server View Trigger Definition"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various ways to view SQL Server trigger definition.<\/p>\n\n\n\n

Getting trigger definition by querying from a system view #<\/a><\/h2>\n\n\n\n

You can get the definition of a trigger by querying data against the sys.sql_modules<\/code> view:<\/p>\n\n\n

SELECT<\/span> \n    definition   \nFROM<\/span> \n    sys.sql_modules  \nWHERE<\/span> \n    object_id = OBJECT_ID('sales.trg_members_delete'<\/span>); \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this query, you pass the name of the trigger which you want to get the definition to the OBJECT_ID()<\/code> function in the WHERE<\/a><\/code> clause.<\/p>\n\n\n\n
Getting trigger definition using OBJECT_DEFINITION function #<\/a><\/h2>\n\n\n\n
You can get the definition of a trigger using the OBJECT_DEFINITION<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span> \n    OBJECT_DEFINITION (\n        OBJECT_ID(\n            'sales.trg_members_delete'<\/span>\n        )\n    ) AS<\/span> trigger_definition;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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()<\/code> function to get the Transact-SQL source text of the definition of a trigger based on its ID.<\/p>\n\n\n\n
Getting trigger definition using sp_helptext stored procedure #<\/a><\/h2>\n\n\n\n
The simplest way to get the definition of a trigger is to use the sp_helptext stored procedure as follows:<\/p>\n\n\n
EXEC sp_helptext 'sales.trg_members_delete' ;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The sp_helptext<\/code> stored procedure returns the definition used to create an object, in this case, a trigger.<\/p>\n\n\n\n
Getting trigger definition using SSMS #<\/a><\/h2>\n\n\n\n
To view the definition of a DML trigger:<\/p>\n\n\n\n
  1. First, in Object Explorer, connect to the database and expand that instance.<\/li>
  2. Second, expand the database and table which contains the trigger that you want to view the definition.<\/li>
  3. Third, expand Triggers, right-click the trigger you want to view the definition, and then click Modify<\/strong>. The trigger definition appears in the query window.<\/li><\/ol>\n\n\n\n
    \"SQL<\/figure>\n\n\n\n
    In this tutorial, you have learned various ways to view the definition of a trigger.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t