DDL trigger

Question

DDL trigger

SQLDCH

SSChampion

Points: 11599
More actions
January 17, 2012 at 10:03 pm

#391332

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.

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

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

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 More actions · Answer 2

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: 150467 More actions · Answer 3

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

DugyC Hall of Fame Points: 3804 More actions · Answer 4

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 More actions · Answer 5

SqlMel

SSCrazy

Points: 2891

January 18, 2012 at 5:06 am

#1435152

Good question. 🙂

---------------
Mel. 😎

TomThomson SSC Guru Points: 104773 More actions · Answer 6

TomThomson

SSC Guru

Points: 104773

January 18, 2012 at 5:15 am

#1435157

Good question. Nice explanation.

Tom

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 7

Mike Is Here

Hall of Fame

Points: 3348

January 18, 2012 at 5:55 am

#1435177

Good thing MSDN is not blacked out.

Cliff Jones SSChampion Points: 10547 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 4759 More actions · Answer 11

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: 12377 More actions · Answer 12

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: 10666 More actions · Answer 13

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

I was hoping for less.

SqlMel SSCrazy Points: 2891 More actions · Answer 14

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. 😎