Get Information About DML Triggers

Article
06/13/2017

This topic describes how to get information about DML triggers in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. 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
To get information about DML triggers, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

Security

Permissions

sys.sql.modules, sys.object, sys.triggers, sys.events, sys.trigger_events
The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

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

In Object Explorer, connect to an instance of Database Engine and then expand that instance.
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.
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

In Object Explorer, connect to an instance of Database Engine and then expand that instance.
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.
Expand Triggers, right-click the trigger you want, and then click View Dependencies.
In the Object Dependencies window, to view the objects that depend on the DML trigger, select Objects that depend on <DML trigger name>. The objects appear in the Dependencies area.

To view the objects on which the DML depends, select Objects on which <DML trigger name> depends. The objects appear in the Dependencies area. Expand each node to see all the objects.
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.
To close the Object Dependencies window, click OK.

Using Transact-SQL

To view the definition of a DML trigger

Connect to the Database Engine.
From the Standard bar, click New Query.
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.

USE AdventureWorks2012;  
GO  
SELECT definition   
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID(N'Person.iuPerson');   
GO

USE AdventureWorks2012;   
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.iuPerson')) AS ObjectDefinition;   
GO

USE AdventureWorks2012;   
GO  
EXEC sp_helptext 'Person.iuPerson'  
GO

To view the dependencies of a DML trigger

Connect to the Database Engine.
From the Standard bar, click New Query.
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

Connect to the Database Engine.
From the Standard bar, click New Query.
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

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

USE AdventureWorks2012;   
GO  
SELECT OBJECTPROPERTY(OBJECT_ID(N'Person.iuPerson'), 'ExecIsInsteadOfTrigger');   
GO

To view information about events that fire a DML trigger

Connect to the Database Engine.
From the Standard bar, click New Query.
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.

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

USE AdventureWorks2012;   
GO   
SELECT object_id, type,is_first, is_last  
FROM sys.trigger_events  
WHERE object_id = OBJECT_ID('Person.iuPerson');   
GO

Share via

Get Information About DML Triggers

Before You Begin

Security

Permissions

Using SQL Server Management Studio

To view the definition of a DML trigger

To view the dependencies of a DML trigger

Using Transact-SQL

To view the definition of a DML trigger

To view the dependencies of a DML trigger

To view information about DML triggers in the database

To view information about events that fire a DML trigger

See Also

Additional resources