sp_helptrigger (Transact-SQL)

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 sys.triggers (Transact-SQL) catalog view instead.

Important

This feature has changed from earlier versions of SQL Server. For more information, see Breaking Changes to Database Engine Features in SQL Server 2005.

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.Contact table.

USE AdventureWorks;
GO
EXEC sp_helptrigger 'Person.Contact'

See Also

Reference

Database Engine Stored Procedures (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance