Tags

, ,


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
Advertisements