Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
.gif "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.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)