sp_helptrigger (Transact-SQL)

03/14/2017
2 minutes to read
Contributors

APPLIES TO: yes SQL Server (starting with 2008) yes Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

Returns the type or types of DML triggers defined on the specified table for the current database. sp_helptrigger cannot be used with DDL triggers. Query the system stored procedures catalog view instead.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

[ **@tabname=** ] 'table'
Is the name of the table in the current database for which to return trigger information. table is nvarchar(776), with no default.

[ **@triggertype=** ] 'type'
Is the type of DML trigger to return information about. type is char(6), with a default of NULL, and can be one of these values.

Value	Description
DELETE	Returns DELETE trigger information.
INSERT	Returns INSERT trigger information.
UPDATE	Returns UPDATE trigger information.

Return Code Values

0 (success) or 1 (failure)

Result Sets

The following table shows the information that is contained in the result set.

Column name	Data type	Description
trigger_name	sysname	Name of the trigger.
trigger_owner	sysname	Name of the owner of the table on which the trigger is defined.
isupdate	int	1=UPDATE trigger 0=Not an UPDATE trigger
isdelete	int	1=DELETE trigger 0=Not a DELETE trigger
isinsert	int	1=INSERT trigger 0=Not an INSERT trigger
isafter	int	1=AFTER trigger 0=Not an AFTER trigger
isinsteadof	int	1=INSTEAD OF trigger 0=Not an INSTEAD OF trigger
trigger_schema	sysname	Name of the schema to which the trigger belongs.

Permissions

Requires Metadata Visibility Configuration permission on the table.

Examples

The following example executes sp_helptrigger to produce information about the trigger(s) on the Person.Person table.

USE AdventureWorks2012;  
GO  
EXEC sp_helptrigger 'Person.Person';