Transact-SQL Reference

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)

See Also

ALTER TRIGGER

CREATE TRIGGER

DROP TRIGGER

System Stored Procedures