//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Schema Tutorials :: Schema: How do I show all the triggers in a database?


Schema: How do I show all the triggers in a database?

The following code uses a pair of nested recordsets to grab the triggers from the sysobjects table and then display them using sp_helptext (tested in SQL Server 2000): 
 
<% 
    dbname = "databasename" 
 
    ConnStr = "provider=SQLOLEDB;network=DBMSSOCN;" 
    ConnStr = ConnStr & "uid=<uid>;pwd=<pwd>;server=" 
    ConnStr = ConnStr & "<x.x.x.x>;database=" & dbname 
     
    set Conn = CreateObject("ADODB.Connection") 
    Conn.Open ConnStr 
     
    set rs = Conn.Execute("SELECT name FROM sysobjects WHERE xtype='TR'") 
    do while not rs.eof 
        response.write("<hr>" & rs(0) & "<br>") 
        set rs2 = Conn.Execute("EXEC sp_helptext '" & rs(0) & "'") 
        do while not rs2.eof 
            Response.Write(rs2(0) & "<br>") 
            rs2.movenext 
        loop 
        rs.movenext 
    loop 
     
    rs.close: set rs = nothing 
    Conn.Close: set Conn = nothing 
%>
 
Note that you must loop through the sp_helptext resultset because, as with stored procedures, each line of code in a trigger is returned as one row in the resultset. 
 
Are there other ways to do this? Sure, here's some sample code, but it should be avoided due to its reliance on an undocumented SP (as well as the fact that it produces a resultset for every table, regardless of whether it has a trigger): 
 
EXEC sp_MSForEachTable 'PRINT ''?'' 
    EXEC sp_helptrigger ''?'''
 
And this code, adapted from a post by Alejandro Mesa - which includes more information, but not the text for each trigger in the current DB: 
 
 
SELECT 
    [Table] = OBJECT_NAME(o.parent_obj), 
    [Trigger] = o.[name], 
    [Type] = CASE WHEN 
        ( 
        SELECT 
            cmptlevel 
        FROM 
            master.dbo.sysdatabases 
        WHERE 
            [name] = DB_NAME() 
        ) = 80 THEN 
        CASE WHEN 
            OBJECTPROPERTY(o.[id], 
            'ExecIsInsteadOfTrigger') = 1 THEN 
                'Instead Of' 
            ELSE 
                'After' 
            END 
        ELSE 
            'After' 
        END, 
    [Insert] = CASE WHEN 
        OBJECTPROPERTY(o.[id], 
        'ExecIsInsertTrigger') = 1 THEN 
            'Yes' 
        ELSE 
            'No' 
        END, 
    [Update] = CASE WHEN 
        OBJECTPROPERTY(o.[id], 
        'ExecIsUpdateTrigger') = 1 THEN 
            'Yes' 
        ELSE 
            'No' 
        END, 
    [Delete] = CASE WHEN  
        OBJECTPROPERTY(o.[id], 
        'ExecIsDeleteTrigger') = 1 THEN 
            'Yes' 
        ELSE 
            'No' 
        END, 
    [Enabled?] = CASE WHEN 
        OBJECTPROPERTY(o.[id], 
        'ExecIsTriggerDisabled') = 0 THEN 
            'Enabled' 
        ELSE 
            'Disabled' 
        END 
FROM 
    sysobjects o 
WHERE 
    OBJECTPROPERTY(o.[id], 'IsTrigger') = 1 
    -- leave out the following clause if you need to 
    -- include system triggers, e.g. those in MSDB 
    AND 
    OBJECTPROPERTY(o.[id], 'IsMSShipped') = 0 
ORDER BY 
    1,2

Related Articles

Schema: How do I list all the indexes in a database?
Schema: How do I list the databases on my server?
Schema: How do I show all the primary keys in a database?
Schema: How do I show the columns for a table?
Schema: How do I show the description property of a column?
Schema: How do I show the parameters for a function or stored procedure?
Schema: How do I show the stored procedures in a database?
Schema: How do I show the tables in a database?
Schema: How do I show the user-defined functions (UDFs) in a database?
Schema: How do I show the views in a SQL Server database?

 

 


Created: 2/25/2001 | Last Updated: 10/5/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (59)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...