Export (0) Print
Expand All
Expand Minimize

sp_helptrigger (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

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

USE AdventureWorks2012;
GO
EXEC sp_helptrigger 'Person.Person';
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft