DDL trigger

  • SQLDCH

    SSChampion

    Points: 11400

    Comments posted to this topic are about the item DDL trigger

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • SQLRNNR

    SSC Guru

    Points: 281210

    Thanks for the ?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    I am not sure I understand the explanation.

    I would expect to find a row for the trigger in sys.all_objects, if it is said to be stored as an "object" in the master database. But sys.all_objects does not contain the server scoped triggers.

    Why is that?

    Best Regards,

    Chris Bรผttner

  • Paul White

    SSC Guru

    Points: 150341

    Christian Buettner-167247 (1/18/2012)


    I am not sure I understand the explanation.

    I would expect to find a row for the trigger in sys.all_objects, if it is said to be stored as an "object" in the master database. But sys.all_objects does not contain the server scoped triggers.

    Why is that?

    The BOL entry for sys.all_objects says:

    "Shows the UNION of all schema-scoped user-defined objects and system objects."

    Server-scoped DDL triggers are not schema-scoped.

  • DugyC

    Hall of Fame

    Points: 3804

    Another lesson not learned about reading the question and answers carefully.

    You can obtain info by querying sys.triggers but they are only stored as objects on the master database. I got too excited and chose true/true... oops!

    I'll call this karma... ๐Ÿ˜‰

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • SqlMel

    SSCrazy

    Points: 2891

    Good question. ๐Ÿ™‚

    ---------------
    Mel. ๐Ÿ˜Ž

  • TomThomson

    SSC Guru

    Points: 104707

    Good question. Nice explanation.

    Tom

  • Mike Is Here

    Hall of Fame

    Points: 3348

    Good thing MSDN is not blacked out.

  • Cliff Jones

    SSChampion

    Points: 10517

    For some reason I thought the question was about database scoped triggers even though it clearly says server scoped. Brain not fully engaged yet. Good question.

  • Dana Medley

    SSCertifiable

    Points: 6764

    Great question.

    I guess it's just a little too early for the brain to be attempting to operate 100%. That's what I get for attempting to answer without some caffeine pumping yet.



    Everything is awesome!

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    SQL Kiwi (1/18/2012)


    The BOL entry for sys.all_objects says:

    "Shows the UNION of all schema-scoped user-defined objects and system objects."

    Server-scoped DDL triggers are not schema-scoped.

    Thanks, that makes sense.

    Best Regards,

    Chris Bรผttner

  • BarbW

    SSCarpal Tunnel

    Points: 4649

    Maybe I misread the question...probably, but I found this on TechNet and still got the answer wrong. Oh well, learned another one the hard way ๐Ÿ™‚

    Server-scoped DDL triggers are stored as objects in the master database. However, you can obtain information about server-scoped DDL triggers by querying the sys.server_triggers catalog view in any database context.

  • sknox

    SSChampion

    Points: 12201

    I got it right for the "wrong" reason: I reasoned that 2 had to be false, because nothing is stored in a view. A view is by definition a mechanism to access data stored elsewhere.

    ๐Ÿ˜›

  • paul s-306273

    SSChampion

    Points: 10527

    My only disappointment is that 49% have got this right!

    I was hoping for less.

  • SqlMel

    SSCrazy

    Points: 2891

    sknox (1/18/2012)


    I got it right for the "wrong" reason: I reasoned that 2 had to be false, because nothing is stored in a view. A view is by definition a mechanism to access data stored elsewhere.

    ๐Ÿ˜›

    Well, Idexed views are stored in the db. Can't blame you for using this reasoning, though.

    ---------------
    Mel. ๐Ÿ˜Ž

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply