--- title: "Get Information About DML Triggers | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: sql ms.reviewer: "" ms.technology: ms.topic: conceptual helpviewer_keywords: - "metadata [SQL Server], triggers" - "viewing DML triggers" - "DML triggers, metadata" - "displaying DML triggers" - "status information [SQL Server], triggers" - "DML triggers, viewing" ms.assetid: 37574aac-181d-4aca-a2cc-8abff64237dc author: "rothja" ms.author: "jroth" monikerRange: "=azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # Get Information About DML Triggers [!INCLUDE[tsql-appliesto-ss2008-asdb-xxxx-xxx-md](../../includes/tsql-appliesto-ss2008-asdb-xxxx-xxx-md.md)] This topic describes how to get information about DML triggers in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../includes/tsql-md.md)]. This information can include the types of triggers on a table, the name of a trigger, its owner and the date it was created or modified. If the trigger was not encrypted when it was created, you obtain the definition of the trigger. You can use the definition to help you understand how a trigger affects the table up on which it is defined. Also, you can find out the objects that a specific trigger uses. With this information, you can identify the objects that affect the trigger if they are changed or deleted in the database. **In This Topic** - **Before you begin:** [Security](#Security) - **To get information about DML triggers, using:** [SQL Server Management Studio](#SSMSProcedure) [Transact-SQL](#TsqlProcedure) ## Before You Begin ### Security #### Permissions **sys.sql.modules**, **sys.object**, **sys.triggers**, **sys.events**, **sys.trigger_events** [!INCLUDE[ssCatViewPerm](../../includes/sscatviewperm-md.md)] For more information, see [Metadata Visibility Configuration](../../relational-databases/security/metadata-visibility-configuration.md). OBJECT_DEFINITION, OBJECTPROPERTY, **sp_helptext** Requires membership in the **public** role. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. These permissions are implicitly held by members of the **db_owner**, **db_ddladmin**, and **db_securityadmin** fixed database roles. **sys.sql_expression_dependencies** Requires VIEW DEFINITION permission on the database and SELECT permission on **sys.sql_expression_dependencies** for the database. By default, SELECT permission is granted only to members of the **db_owner** fixed database role. When SELECT and VIEW DEFINITION permissions are granted to another user, the grantee can view all dependencies in the database. ## Using SQL Server Management Studio #### To view the definition of a DML trigger 1. In **Object Explorer**, connect to an instance of [!INCLUDE[ssDE](../../includes/ssde-md.md)] and then expand that instance. 2. Expand the database that you want, expand **Tables**, and then expand the table that contains the trigger for which you want to view the definition. 3. Expand **Triggers**, right-click the trigger you want, and then click **Modify**. The definition of the DML trigger appears in the query window. #### To view the dependencies of a DML trigger 1. In **Object Explorer**, connect to an instance of [!INCLUDE[ssDE](../../includes/ssde-md.md)] and then expand that instance. 2. Expand the database that you want, expand **Tables**, and then expand the table that contains the trigger and its dependencies that you want to view. 3. Expand **Triggers**, right-click the trigger you want, and then click **View Dependencies**. 4. In the **Object Dependencies** window, to view the objects that depend on the DML trigger, select **Objects that depend on \**. The objects appear in the **Dependencies** area. To view the objects on which the DML depends, select **Objects on which \ depends**. The objects appear in the **Dependencies** area. Expand each node to see all the objects. 5. To obtain information about an object that appears in the **Dependencies** area, click the object. In the **Selected object** field, information is provided in the **Name**, **Type**, and **Dependency type** boxes. 6. To close the **Object Dependencies** window, click **OK**. ## Using Transact-SQL #### To view the definition of a DML trigger 1. Connect to the [!INCLUDE[ssDE](../../includes/ssde-md.md)]. 2. From the Standard bar, click **New Query**. 3. Copy and paste one of the following examples into the query window and click **Execute**. Each example shows how you can view the definition of the `iuPerson` trigger. ```sql USE AdventureWorks2012; GO SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID(N'Person.iuPerson'); GO ``` ```sql USE AdventureWorks2012; GO SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.iuPerson')) AS ObjectDefinition; GO ``` ```sql USE AdventureWorks2012; GO EXEC sp_helptext 'Person.iuPerson' GO ``` #### To view the dependencies of a DML trigger 1. Connect to the [!INCLUDE[ssDE](../../includes/ssde-md.md)]. 2. From the Standard bar, click **New Query**. 3. Copy and paste one of the following examples into the query window and click **Execute**. Each example shows how you can view the dependencies of `iuPerson` trigger. ``` USE AdventureWorks2012; GO SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name, o.type_desc AS referencing_desciption, COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id, referencing_class_desc, referenced_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name, is_caller_dependent, is_ambiguous FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referencing_id = OBJECT_ID(N'Person.iuPerson'); GO ``` #### To view information about DML triggers in the database 1. Connect to the [!INCLUDE[ssDE](../../includes/ssde-md.md)]. 2. From the Standard bar, click **New Query**. 3. Copy and paste one of the following examples into the query window and click **Execute**. Each example shows how you can view information about DML triggers (`TR`) in the database. ``` USE AdventureWorks2012; GO SELECT name, parent_id, create_date, modify_date, is_instead_of_trigger FROM sys.triggers WHERE type = 'TR'; GO ``` ```sql USE AdventureWorks2012; GO SELECT name, object_id, schema_id, parent_object_id, type_desc, create_date, modify_date, is_published FROM sys.objects WHERE type = 'TR'; GO ``` ```sql USE AdventureWorks2012; GO SELECT OBJECTPROPERTY(OBJECT_ID(N'Person.iuPerson'), 'ExecIsInsteadOfTrigger'); GO ``` #### To view information about events that fire a DML trigger 1. Connect to the [!INCLUDE[ssDE](../../includes/ssde-md.md)]. 2. From the Standard bar, click **New Query**. 3. Copy and paste one of the following examples into the query window and click **Execute**. Each example shows how you can view the events that fire the `iuPerson` trigger. ```sql USE AdventureWorks2012; GO SELECT object_id, type, type_desc, is_trigger_event, event_group_type, event_group_type_desc FROM sys.events WHERE object_id = OBJECT_ID('Person.iuPerson'); GO ``` ```sql USE AdventureWorks2012; GO SELECT object_id, type,is_first, is_last FROM sys.trigger_events WHERE object_id = OBJECT_ID('Person.iuPerson'); GO ``` ## See Also [CREATE TRIGGER (Transact-SQL)](../../t-sql/statements/create-trigger-transact-sql.md) [DROP TRIGGER (Transact-SQL)](../../t-sql/statements/drop-trigger-transact-sql.md) [ENABLE TRIGGER (Transact-SQL)](../../t-sql/statements/enable-trigger-transact-sql.md) [DISABLE TRIGGER (Transact-SQL)](../../t-sql/statements/disable-trigger-transact-sql.md) [EVENTDATA (Transact-SQL)](../../t-sql/functions/eventdata-transact-sql.md) [sp_rename (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-rename-transact-sql.md) [ALTER TRIGGER (Transact-SQL)](../../t-sql/statements/alter-trigger-transact-sql.md) [sp_help (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-help-transact-sql.md) [sp_helptrigger (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-helptrigger-transact-sql.md) [sys.triggers (Transact-SQL)](../../relational-databases/system-catalog-views/sys-triggers-transact-sql.md) [sys.trigger_events (Transact-SQL)](../../relational-databases/system-catalog-views/sys-trigger-events-transact-sql.md) [sys.sql_modules (Transact-SQL)](../../relational-databases/system-catalog-views/sys-sql-modules-transact-sql.md) [sys.assembly_modules (Transact-SQL)](../../relational-databases/system-catalog-views/sys-assembly-modules-transact-sql.md) [sys.server_triggers (Transact-SQL)](../../relational-databases/system-catalog-views/sys-server-triggers-transact-sql.md) [sys.server_trigger_events (Transact-SQL)](../../relational-databases/system-catalog-views/sys-server-trigger-events-transact-sql.md) [sys.server_sql_modules (Transact-SQL)](../../relational-databases/system-catalog-views/sys-server-sql-modules-transact-sql.md) [sys.server_assembly_modules (Transact-SQL)](../../relational-databases/system-catalog-views/sys-server-assembly-modules-transact-sql.md) [OBJECTPROPERTY (Transact-SQL)](../../t-sql/functions/objectproperty-transact-sql.md) [OBJECT_DEFINITION (Transact-SQL)](../../t-sql/functions/object-definition-transact-sql.md)