DDL trigger

Question

Post reply

DDL trigger

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

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

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 1

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

January 18, 2012 at 8:40 am

#1435302

tks for the question

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 2

BarbW (1/18/2012)
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.

"Which of these statements is true?

Server-scoped DDL triggers are stored as objects in the [master] database.

Server-scoped DDL triggers are stored in the 'sys.triggers' catalog view in the [master] database."

THe view you mention is sys.server_triggers, not sys.triggers.

Ken SSCoach Points: 17191 More actions · Answer 3

Ken

SSCoach

Points: 17191

January 18, 2012 at 9:24 am

#1435353

Thanks for the question

Revenant SSC-Forever Points: 42467 More actions · Answer 4

Revenant

SSC-Forever

Points: 42467

January 18, 2012 at 9:28 am

#1435359

Thanks for the question, Dan!

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

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

I used the same reasoning. 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.

Thanks for the question, I learned something even though I got it right. 🙂

zymos SSCommitted Points: 1960 More actions · Answer 6

Great question on the 'Server-Scoped DDL trigger' that was tricky and needed a better understanding using the tip on of the main question.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 7

Good question. Thanks for submitting.

http://brittcluff.blogspot.com/

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Thanks for the effort and great question.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gatekeeper SSCommitted Points: 1633 More actions · Answer 10

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? */

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 11

Nice Question .Thanks

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Haining SSCrazy Points: 2118 More actions · Answer 12

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

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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%5B/quote%5D

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

SQLDCH SSChampion Points: 11630 More actions · Answer 14

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 15

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