Which system trigger fires?

Question

Which system trigger fires?

Aadhar Joshi

Hall of Fame

Points: 3202
More actions
December 9, 2012 at 9:27 pm

#285981

When system trigger fires when we use below code
sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
i need to write insted of triggers on that

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no system triggers on the configurations view, and I doubt you can write triggers on a system view, even if you can, you should not.

What are you trying to do here?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Aadhar Joshi Hall of Fame Points: 3202 More actions · Answer 2

Thanks GilaMonster,

Actually i need to create trigger through which i can track every change happen in sys.configurations so later on i should know what value has changed of what attribute..

I searched many things but i am clueless. please help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Nope, that's not something you'd use a trigger for. That's something you'd probably use an extended events sessions for, or SQL Trace if you're using SQL 2005 or below.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Aadhar Joshi Hall of Fame Points: 3202 More actions · Answer 4

I'm using sql server 2008 r2.

I will check possible solutions as u mentioned tip..

anything that would help me more, please suggest..

GSquared SSC Guru Points: 260824 More actions · Answer 5

Here's a list of what you can track with triggers (DDL triggers, to be precise): http://msdn.microsoft.com/en-us/library/ms189871(v=SQL.90).aspx

Here's some data on Extended Events: http://msdn.microsoft.com/en-us/library/bb630354(v=SQL.105).aspx

As Gail mentioned, Extended Events are probably what you're looking for. But you might be able to get some use out of DDL triggers. Depends on what all you want to track.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon