sp_helptrigger (Transact-SQL)

  • Article

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.

Topic link iconTransact-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