Transact-SQL User's Guide

Getting Information About Triggers

As database objects, triggers are listed in sysobjects by name. The type column of sysobjects identifies triggers with the abbreviation "TR". This query finds the triggers that exist in a database:

select
* 
from sysobjects 
where type = "TR" 

The source text for each trigger is stored in syscomments. Execution plans for triggers are stored in sysprocedures. The system procedures described in the following sections provide information from the system tables about triggers.

sp_help

You can get a report on a trigger with the system procedure sp_help. For example, you can get information on deltitle as follows:

sp_help deltitle

Name        Owner   Type        
----------- -------
----------- 
deltitle    dbo     trigger

Data_located_on_segment
When_created
-----------------------  ----------------- 
not
applicable          Jul 10 1997 3:56PM

 
(return status =
0)

sp_helptext

To display the source text of a trigger, execute the system procedure sp_helptext, as follows:

sp_helptext deltitle 

# Lines of
Text
---------------
              1

text

--------------------------------------------- 

create
trigger deltitle 
on titles 
for delete 
as 
if (select
count(*) from deleted, salesdetail 
where salesdetail.title_id =
deleted.title_id) >0 
begin 
      rollback transaction 

print "You can't delete a title with sales." 
end 

If the source text of a trigger was encrypted using sp_hidetext, Adaptive Server displays a message advising you that the text is hidden. For information about hiding source text, see sp_hidetext in the Adaptive Server Reference Manual.

If the System Security Officer has reset the allow select on syscomments.text column parameter with the system procedure sp_configure (as required to run Adaptive Server in the evaluated configuration), you must be the creator of the trigger or a System Administrator to view the source text of a trigger through sp_helptext. (See evaluated configuration in the Adaptive Server Glossary for more information.)

sp_depends

The system procedure sp_depends lists the triggers that reference an object or all the tables or views that the trigger affects. This example shows how to use sp_depends to get a list of all the objects referenced by the trigger deltitle:

sp_depends deltitle 

Things the object references in
the current database. 

object              type
updated  selected 
----------------    ----------  ------- --------

dbo.salesdetail     user table  no      no
dbo.titles
user table  no      no
 
(return status = 0)

This statement lists all the objects that reference the salesdetail table:

sp_depends salesdetail 

Things inside the
current database that reference the object.
 
object
       type         
---------------------------  ----------------

dbo.deltitle                 trigger         
dbo.history_proc
        stored procedure  
dbo.insert_salesdetail_proc  stored
procedure
dbo.totalsales_trig          trigger 
 
(return
status = 0)