Export (0) Print
Expand All

Get Information About DML Triggers

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

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.

Arrow icon used with Back to Top link [Top]

To view the definition of a DML trigger

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

  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.

Arrow icon used with Back to Top link [Top]

To view the definition of a DML trigger

  1. Connect to the Database Engine.

  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.

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

  1. Connect to the Database Engine.

  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 Database Engine.

  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
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

  1. Connect to the Database Engine.

  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.

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

Arrow icon used with Back to Top link[Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft