Displaying Information About Triggers

Because they are database objects, triggers are listed in the sysobjects system table by name. The type column in sysobjects identifies triggers with the abbreviation TR. Execution plans for triggers are stored in sysprocedures.

This query finds the triggers in a database:

SELECT *
FROM sysobjects
WHERE type = 'TR'

name	id	uid	type	userstat	sysstat	indexdel
---------	-------	---	----	--------	-------	--------
employee_ insupd	832005995	1	TR	0	8	0

schema	refdate	crdate	version	deltrig
------	-------------------	-----------	-------	-------
0	May 26 1995 12:44PM	May 26 1995	12:44PM	0

instrig	updtrig	seltrig	category	cache
-----------	-----------	-----------	-----------	-----
688005482	0	0	0	0

(1 row(s) affected)

To display information about a trigger

Execute the sp_help system stored procedure.

For example, to get information on the deltrig trigger:

sp_help deltrig

Name	Owner	Type	When_created
--------	-----	-------	-----------------
deltrig	dbo	trigger	June 14 1995 6:10AM

Data_located_on_segment
-----------------------
not applicable

The CREATE TRIGGER statement for each trigger is stored in the syscomments system table. You can display the trigger definition by using the sp_helptext system procedure:

sp_helptext deltrig
text
-------------------------------------------
create trigger deltrig
on sales
for delete
as
.
.
.