January 17, 2012 at 10:03 pm
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
----------------------------------------------------------------------------
January 17, 2012 at 11:55 pm
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
January 18, 2012 at 1:45 am
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
January 18, 2012 at 3:01 am
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.
January 18, 2012 at 3:10 am
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]
January 18, 2012 at 5:06 am
Good question.
---------------
Mel.
January 18, 2012 at 5:15 am
Good question. Nice explanation.
Tom
January 18, 2012 at 5:55 am
Good thing MSDN is not blacked out.
January 18, 2012 at 6:53 am
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.
January 18, 2012 at 7:01 am
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.
January 18, 2012 at 7:13 am
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
January 18, 2012 at 7:36 am
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.
January 18, 2012 at 8:13 am
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.
January 18, 2012 at 8:27 am
My only disappointment is that 49% have got this right!
I was hoping for less.
January 18, 2012 at 8:27 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy