Professional Web Applications Themes

Finding trigger in Enterprise manager - Microsoft SQL / MS SQL Server

Hi, i had created a trigger few days back on one of the tables. How do i find out the text associated with the trigger and to which table it is associated. I tried using enterprise manager,but it does not have triggers as one of the nodes in the tree view. One option is I use ms visio and extract all the objects from database and after i'll be able to locate the trigger in the visio, but this is very long route. I am sure there must be a shortcut route in finding this out. Thanks for help extended. ...

  1. #1

    Default Finding trigger in Enterprise manager

    Hi,

    i had created a trigger few days back on one of the
    tables.
    How do i find out the text associated with the trigger and
    to which table it is associated. I tried using enterprise
    manager,but it does not have triggers as one of the nodes
    in the tree view.

    One option is I use ms visio and extract all the objects
    from database and after i'll be able to locate the trigger
    in the visio, but this is very long route. I am sure there
    must be a shortcut route in finding this out.

    Thanks for help extended.

    Regards
    amit
    Amit Guest

  2. #2

    Default Re: Finding trigger in Enterprise manager

    Hi Amit,

    Right click the table in Enterprise Manager, then choose All Tasks -> Manage
    Triggers... and select your trigger from the Name dropdown box at the top.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Amit" <com> wrote in message
    news:0a2301c366fe$b2131ff0$gbl... 


    Jacco Guest

  3. #3

    Default Re: Finding trigger in Enterprise manager

    Hi Jacco Schalkwijk,
    Yes got it.
    Is there any other way to find out all the triggers
    created in the database.
    Just in case for worst case scenario: if I don't remember
    the table for which i've created the trigger then how do i
    find it out. As per the mechanism suggested, i have to
    explicitly search for each table in the EM.

    I am sure there must be some other way out in ms sql
    server 2000 as is there in oracle.

    Regards,
    Amit
     
    All Tasks -> Manage 
    dropdown box at the top. [/ref]
    and [/ref]
    enterprise [/ref]
    nodes [/ref]
    trigger [/ref]
    there 
    >
    >
    >.
    >[/ref]
    Amit Guest

  4. #4

    Default Re: Finding trigger in Enterprise manager

    Hi Amit,

    You can get an overview of all the triggers on all the tables if you run the
    following code from Query yzer:
    SELECT OBJECT_NAME(parent_obj) AS TableName,
    OBJECT_NAME(id) AS TriggerName
    FROM sysobjects
    WHERE OBJECTPROPERTY(id, 'IsTrigger') =1

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Amit" <com> wrote in message
    news:040f01c36706$070eeff0$gbl... 
    > All Tasks -> Manage 
    > dropdown box at the top. [/ref]
    > and [/ref]
    > enterprise [/ref]
    > nodes [/ref]
    > trigger [/ref]
    > there 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Jacco Guest

  5. #5

    Default Re: Finding trigger in Enterprise manager

    You can get a list of all triggers with this script.

    select 'CREATE TRIGGER ' + user_name(trig.uid) + '.' + trig.name as [CREATE
    TRIGGER]
    , 'ON ' + user_name(tab.uid) + '.' + tab.name as [ON]
    , case when ObjectProperty( trig.id, 'ExecIsInsteadOfTrigger') = 1 then
    'INSTEAD OF ' else 'FOR ' end
    + substring(case when ObjectProperty( trig.id, 'ExecIsInsertTrigger') = 1
    then ', INSERT' else '' end
    + case when ObjectProperty( trig.id, 'ExecIsUpdateTrigger') = 1 then ',
    UPDATE' else '' end
    + case when ObjectProperty( trig.id, 'ExecIsDeleteTrigger') = 1 then ',
    DELETE' else '' end, 3, 100) as [FOR]
    from sysobjects as trig
    inner join sysobjects as tab on tab.id = trig.parent_obj
    where trig.type = 'TR'
    order by tab.name

    Anthony

    "Amit" <com> wrote in message
    news:0a2301c366fe$b2131ff0$gbl... 


    Anthony Guest

Similar Threads

  1. Enterprise Manager
    By Captain in forum Coldfusion Server Administration
    Replies: 2
    Last Post: June 8th, 03:37 PM
  2. Finding a Key trigger in another program
    By W. Locke Morgan in forum Macromedia Director Lingo
    Replies: 12
    Last Post: September 29th, 07:39 PM
  3. SQL Server 7.0 & Enterprise Manager
    By Darren in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 14th, 02:19 PM
  4. ASP enterprise manager ?
    By Jurjen de Groot in forum ASP.NET General
    Replies: 3
    Last Post: July 11th, 08:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139