sp_helptrigger (Transact-SQL)

Artikel
2024-05-21

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns the type or types of data manipulation language (DML) triggers defined on the specified table for the current database. sp_helptrigger can't be used with data definition language (DDL) triggers. Query the sys.triggers catalog view instead.

Transact-SQL syntax conventions

Syntax

sp_helptrigger
    [ @tabname = ] N'tabname'
    [ , [ @triggertype = ] 'triggertype' ]
[ ; ]

Arguments

[ @tabname = ] N'tabname'

The name of the table in the current database for which to return trigger information. @tabname is nvarchar(776), with no default.

[ @triggertype = ] 'triggertype'

The type of DML trigger to return information about. @triggertype is char(6), 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 set

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 triggers on the Person.Person table in the AdventureWorks2022 database.

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

Dela via