Export (0) Print
Expand All
Expand Minimize

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.

ms189836.note(en-US,SQL.90).gifImportant:
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 icon Transact-SQL Syntax Conventions


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

[ @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.

0 (success) or 1 (failure)

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.

Requires Metadata Visibility Configuration permission on the table.

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'

Community Additions

ADD
Show:
© 2014 Microsoft