Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

DDL trigger Expand / Collapse
Author
Message
Posted Tuesday, January 17, 2012 10:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 18, 2014 4:12 PM
Points: 1,194, Visits: 2,666
Comments posted to this topic are about the item DDL trigger

----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Post #1237661
Posted Tuesday, January 17, 2012 11:55 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 20,483, Visits: 14,129
Thanks for the ?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1237684
Posted Wednesday, January 18, 2012 1:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
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
Post #1237733
Posted Wednesday, January 18, 2012 3:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1237780
Posted Wednesday, January 18, 2012 3:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
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...


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1237784
Posted Wednesday, January 18, 2012 5:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:31 AM
Points: 421, Visits: 177
Good question.

---------------
Mel.

It's no fun if it's not complicated.

Post #1237866
Posted Wednesday, January 18, 2012 5:14 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 8,295, Visits: 8,746
Good question. Nice explanation.

Tom
Post #1237872
Posted Wednesday, January 18, 2012 5:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:27 AM
Points: 1,378, Visits: 454
Good thing MSDN is not blacked out.
Post #1237898
Posted Wednesday, January 18, 2012 6:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 3,772, Visits: 3,588
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.
Post #1237928
Posted Wednesday, January 18, 2012 7:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 1,726, Visits: 1,253
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.




Yeah, uh huh, you know what it is. Everything I do, I do it big

Post #1237934
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse