In SQL Server, you can use the sp_helptrigger stored procedure to return the type or types of DML triggers defined on the specified table for the current database.

This stored procedure only works on DML triggers (not DDL triggers).

Syntax

The official syntax goes like this:

sp_helptrigger [ @tabname = ] 'table'   
     [ , [ @triggertype = ] 'type' ]

You must provide the table name as an argument. You can prefix it with @tabname = , but this is optional.

You also have the option of specifying a trigger type. This can be either INSERT, DELETE, or UPDATE.

Example 1 – Basic Usage

Here’s a simple example to demonstrate.

EXEC sp_helptrigger 'dbo.t1';

Result:

+-------------------+-----------------+------------+------------+------------+-----------+---------------+------------------+
| trigger_name      | trigger_owner   | isupdate   | isdelete   | isinsert   | isafter   | isinsteadof   | trigger_schema   |
|-------------------+-----------------+------------+------------+------------+-----------+---------------+------------------|
| t1_insert_trigger | dbo             | 0          | 0          | 1          | 1         | 0             | dbo              |
+-------------------+-----------------+------------+------------+------------+-----------+---------------+------------------+

In case you have trouble scrolling sideways, here’s the result again using vertical output:

trigger_name   | t1_insert_trigger
trigger_owner  | dbo
isupdate       | 0
isdelete       | 0
isinsert       | 1
isafter        | 1
isinsteadof    | 0
trigger_schema | dbo

Example 2 – Specify a Type

In this example I also specify the type.

EXEC sp_helptrigger 'dbo.t1', 'INSERT';

Result (using vertical output):

trigger_name   | t1_insert_trigger
trigger_owner  | dbo
isupdate       | 0
isdelete       | 0
isinsert       | 1
isafter        | 1
isinsteadof    | 0
trigger_schema | dbo

In this case, there was a trigger of that type.

Here it is again using a different type.

EXEC sp_helptrigger 'dbo.t1', 'DELETE';

Result:

(0 rows affected)

In this case there were no DELETE triggers on that table.