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

DELETEReturns DELETE trigger information.
INSERTReturns INSERT trigger information.
UPDATEReturns UPDATE trigger information.

0 (success) or 1 (failure)

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

Column nameData typeDescription
trigger_namesysnameName of the trigger.
trigger_ownersysnameName of the owner of the table on which the trigger is defined.
isupdateint1=UPDATE trigger

0=Not an UPDATE trigger
isdeleteint1=DELETE trigger

0=Not a DELETE trigger
isinsertint1=INSERT trigger

0=Not an INSERT trigger
isafterint1=AFTER trigger

0=Not an AFTER trigger
isinsteadofint1=INSTEAD OF trigger

0=Not an INSTEAD OF trigger
trigger_schemasysnameName 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;  
EXEC sp_helptrigger 'Person.Person';  

Database Engine Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions