T-SQL » Triggers » Viewing a Trigger

Syntax:
  1. To view the types of triggers on a table:
    sp_helptrigger table1 [ , type ]

  2. To view a trigger:
    sp_helptext trigger1
table1
Is the name of the table for which to return trigger information.
type
Is an optional parameter where the type of the trigger is to be specified. Its value can be INSERT, UPDATE or DELETE.
trigger1
Is the name of the trigger for which the definition information is to be displayed.

SQL server allows us to determine the types of triggers on a table, view information about a trigger, to view a trigger, and to view the dependencies of it.

Examples

Code:
sp_helptrigger Students;
Output:
trigger_nametrigger_ownerisupdateisdeleteisinsert
trigAddStudentsNewdbo001





(1 row(s) affected)
Explanation:

The above example returns all the information about the triggers on the 'Students' table - the name of the triggers, type of the triggers etc.

Language(s): MS SQL Server
Code:
sp_helptrigger Students, 'UPDATE';
Output:
trigger_nametrigger_ownerisupdateisdeleteisinsert
(0 row(s) affected)
Explanation:

If we specify the type of the trigger, we only get the information about that type of trigger.

Language(s): MS SQL Server
Code:
sp_helptext trigAddStudentsNew;
Output:
CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
PRINT 'THE TRIGGER IS CHANGED.'
Explanation:

Here the trigger text is displayed.

Language(s): MS SQL Server