Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    40

    Unanswered: triggers in information schema

    Is there an information schema view that lists all the triggers?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    INFORMATION_SCHEMA.ROUTINES

  3. #3
    Join Date
    Jan 2004
    Posts
    40
    Originally posted by HanafiH
    INFORMATION_SCHEMA.ROUTINES
    i just added a trigger, and it wasn't in the information_schema.routines view. I see it in the sysobjects and syscomments tables.

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    You're quite right. BOL clearly states that .ROUTINES should return triggers, but if you look at the code for the view in master, it omits to filter on triggers.

    ---
    where
    o.xtype IN ('P','FN','TF', 'IF')
    ---

    TR?

    One for Microsoft.

    If you want the views functionality you might be able to copy it into the local db, otherwise, its syscomments. Hey Ho.

  5. #5
    Join Date
    Jan 2004
    Posts
    40
    hm... never though of checking the code in the master db, that is a good idea.

    i don't really mind querying the system tables, but i've read that it is a bad idea, and i should using the information_schema views only. Could someone explain whats wrong with querying the system tables?

    thanks

  6. #6
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    I don't there's anything wrong with querying system tables. But as a good habit, using infromation_schema views will help you avoid potential code breaks resulting from system table schema changes. Microsoft is to make sure that these views stay the same no matter how they may change the base tables in future release.

  7. #7
    Join Date
    Nov 2003
    Posts
    94
    Originally posted by fattyacid
    hm... never though of checking the code in the master db, that is a good idea.

    i don't really mind querying the system tables, but i've read that it is a bad idea, and i should using the information_schema views only. Could someone explain whats wrong with querying the system tables?

    thanks
    OK the issue is forwards compatiblity. The system tables could very easily change in subtle but code-breaking ways between releases of SQL Server. The information_schema views are guaranteed to stay the same, well as guaranteed as anything from Microsoft is.

    So syswhatever is quite safe to use, but you'll need to check when again Yukon comes along.

    Sheesh. I should read the whole thread before replying.
    Last edited by HanafiH; 01-29-04 at 14:09.

Posting Permissions

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