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 Wednesday, January 18, 2012 3:00 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
Great question on the 'Server-Scoped DDL trigger' that was tricky and needed a better understanding using the tip on of the main question.
Post #1238330
Posted Thursday, January 19, 2012 5:28 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Good question. Thanks for submitting.

http://brittcluff.blogspot.com/
Post #1238572
Posted Thursday, January 19, 2012 5:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:35 AM
Points: 1,825, Visits: 2,175
Thanks for the effort and great question.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1238574
Posted Tuesday, January 24, 2012 3:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
Nice question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1240711
Posted Monday, January 30, 2012 10:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 14, 2014 7:53 AM
Points: 393, Visits: 812
cfradenburg (1/18/2012)
I probably would have used "viewed" instead of "stored" but at least it's not a wording choice that makes it more likely for people to get it wrong.


Yep, I would have preferred to see viewed instead of stored.


/* Anything is possible but is it worth it? */
Post #1243774
Posted Monday, March 19, 2012 2:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Nice Question .Thanks

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1268844
Posted Monday, May 21, 2012 12:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 25, 2012 7:39 AM
Points: 1,682, Visits: 210
The answer given for the question is not quite consistent with SQL 2012.
In fact, the SERVER scope DDL triggers present as,

"DDL triggers are not scoped to schemas. Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used for querying metadata about DDL triggers. Use the catalog views instead.

Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL triggers appear in the Database Triggers folder. This folder is located under the Programmability folder of the corresponding database."

Reference: http://msdn.microsoft.com/en-us/library/ms175941.aspx
Post #1303188
Posted Monday, May 21, 2012 2:37 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 8,844, Visits: 9,406
Haining (5/21/2012)
The answer given for the question is not quite consistent with SQL 2012.
In fact, the SERVER scope DDL triggers present as,

"DDL triggers are not scoped to schemas. Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used for querying metadata about DDL triggers. Use the catalog views instead.

Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL triggers appear in the Database Triggers folder. This folder is located under the Programmability folder of the corresponding database."

Reference: http://msdn.microsoft.com/en-us/library/ms175941.aspx

It seems to me that unless I've horribly misunderstood something, the answer is completely consistent with SQL Server 2012. See the SQL 2012 BoL page sys.server_triggers. The page you refer to is about where things appear in SSMS, not about where they are stored, which for server scoped triggers is as in the answer. The answer also points out that database scoped triggers are visible in each database in the appropriate sys.triggers view in the appropriate database, which is confirmed by the relevant SQL 2012 BoL page sys.triggers as well as by the one you quote.

It's a pity that the answer references a BoL page for which there's no SQL 20112 version insetad of one which covers all three currently supported releases.


Tom
Post #1303731
Posted Monday, May 21, 2012 2:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 1,321, Visits: 2,809
The link takes you to the SQL Server 2012 entry, it just doesn't tell you that very well. Look on the left hand navigation hierarchy, it shows 'Books Online for SQL Server 2012'.

The SQL Server 2008 R2 reference is located here (notice the versioning in the parentheses):

http://msdn.microsoft.com/en-us/library/ms175941(v=sql.105).aspx

The SQL Server 2008 reference is located here:

http://msdn.microsoft.com/en-us/library/ms175941(v=sql.100).aspx

Unfortunately when you navigate to the above links, it doesn't give the opportunity to get back to the 2012 version.


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

Yeah, well...The Dude abides.
Post #1303739
Posted Monday, May 21, 2012 3:29 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 8,844, Visits: 9,406
SQLDCH (5/21/2012)
The link takes you to the SQL Server 2012 entry, it just doesn't tell you that very well. Look on the left hand navigation hierarchy, it shows 'Books Online for SQL Server 2012'.

I feel a bit stupid to have missed that!


Tom
Post #1303765
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse