sp_helptrigger
Returns the type or types of triggers defined on the specified table for the current database.
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 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
This table shows the information contained in the result set.
| Column name | Data type | Description |
|---|---|---|
| trigger_name | sysname | Name of the trigger. |
| trigger_owner | sysname | Name of the trigger owner. |
| 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 |
Permissions
Execute permissions default to the public role.
Examples
This example creates a trigger named sales_warn that raises error 50010 when the amount of books sold is 10. Then, sp_helptrigger is executed to produce information about the trigger(s) on the sales table.
USE pubs
CREATE TRIGGER sales_warn
ON sales
FOR INSERT, UPDATE
AS RAISERROR (50010, 16, 10)
EXEC sp_helptrigger sales
Here is the result set:
trigger_name trigger_owner isupdate isdelete isinsert
------------- ----------------------- ----------- ----------- ---------
sales_warn dbo 1 0 1
(1 row(s) affected)