sp_helptrigger (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

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

USE AdventureWorks2012;  
GO  
EXEC sp_helptrigger 'Person.Person';  

See Also

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