Getting Information About DML Triggers

In SQL Server 2005, you can determine the types of triggers on a table, the name of the trigger, its owner, and the date it was created.

You can also perform the following:

• Gain information about the definition of a trigger if it was not encrypted when created or modified. You may have to see the definition of the trigger to see its Transact-SQL statements or to understand how it affects the table upon which it is defined.

• List the objects used by the specified trigger. This information can be used to identify the objects that affect the trigger if they are changed or deleted in the database.

The OBJECTPROPERTY function reports whether a trigger is an AFTER or INSTEAD OF trigger through the ExecIsInsteadOfTrigger and ExecIsAfterTrigger properties.

To obtain information about triggers in the database

• sys.triggers (Transact-SQL)

To obtain information about events that fire a trigger

• sys.trigger_events (Transact-SQL)

• sys.events (Transact-SQL)

  This includes events that fire both event notifications and triggers.

To view the definition of a trigger

• sys.sql_modules (Transact-SQL)

• sp_helptext (Transact-SQL)

To view the dependencies of a trigger

• sys.sql_expression_dependencies (Transact-SQL)

• sys.dm_sql_referenced_entities (Transact-SQL)

• sys.dm_sql_referencing_entities (Transact-SQL)