MS-SQL - List trigger in your db.

Trigger is special set of SQL code where activation is caused by modifications to the tables. (by an INSERT,DELETE or UPDATE).

Trigger is associated with database table, so if you want to find it from your SQL Enterprise Manager or your SQL Query Analyzer, you need to go table by table to find where the trigger is. [see below picture]

trigger

If you don’t want to find it that way, no worries. There is easy way how to find it. Just use simple query and all your triggers associated to which table will be list down.

select B.Name as TableName,A.name as TriggerName
from sysobjects A,sysobjects B
where A.xtype='TR'
AND A.parent_obj = B.id

You will get a result like below.

trigger2

There are a lot of thing that you can play around with sysobjects in MS-SQL.

Here are the list of all possible values for this column (xtype):

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure