Trigger on a system table

Question

Trigger on a system table

Shas3

Hall of Fame

Points: 3671
More actions
December 19, 2005 at 12:30 pm

#110502

Folks, My question is more on MS policies than technical. I am planning on writing a trigger on system table sysconfigures, to notify the DBA's if there is any change in sys configure options. Does it become an issue with MS Supporting the system in case if you need any help from MS down the line ?
Appreciate your response
Thanks
Shas3

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Yes the db will become unsupported. Also triggers on system tables don't always work because some changes are handled entirely by the server (without actually using update statements >> making the trugger not fire).

Shas3 Hall of Fame Points: 3671 More actions · Answer 2

Thanks for the reply Rgr'us. Brain or Andy can you please comment on this if you know any details or links to MS Support on SQL Server 2000.

Thanks

Shas3

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I'd check in the server's EULA for system tables. You'll most likely find that info there.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Triggers are not allowed on SQL Server 2000 system tables and any attempt to create a trigger will cause the the following error message:

Server: Msg 229, Level 14, State 5, Procedure sysobjects_tia, Line 4

CREATE TRIGGER permission denied on object 'sysobjects', database 'tempdb', owner 'dbo'.

To reproduce, try running:

create trigger sysobjects_tia

on dbo.sysobjects for insert

as

select * from inserted

go

SQL = Scarcely Qualifies as a Language

Shas3 Hall of Fame Points: 3671 More actions · Answer 5

There are certain systable that you can not create triggers like sysobjects, however you can create a trigger on sysconfigures, make sure enable the option "allow changes to system tables" option.

Shas3

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

As I said, it's possible but it's a worst practice, at best. Do at your own risks.

Shas3 Hall of Fame Points: 3671 More actions · Answer 7

Point take Rgr. I am looking for other's opinion as well. Thanks for your post again.

Shas3

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

How much more opinions do you need?

Anyway, if you also like to overclock your servers, check this out: http://www.sqlservercentral.com/columnists/rmarda/letsblockthedba.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 9

Shas3,

A long time ago, in a galaxy not so far away, Robert Marda experimented with triggers on system tables and posted the results in these very forums. His results showed that while you can get triggers on some of the system tables, you could not guarantee they would fire, just as what has been posted here by Remi. Also, as has been pointed out, if you do put a trigger on, you do have a situation where you've brought your database to an unsupported state.

If you're trying to check the sysconfigures, why not do this? Dump the results of sysconfigures to a table in a work database. Every five to ten minutes run a SQL Server agent job that compares the config and status columns with what is currently in sysconfigures with what you have stored. If there's a difference, fire off your alert. That'll accomplish the same result as far as notifying about changes. If you need to know "who done it," you can have a sql trace logging statement execution against the master database, could you not?

K. Brian Kelley
@kbriankelley

Shas3 Hall of Fame Points: 3671 More actions · Answer 10

Brain thanks for your reply. In fact that's exactly what we have in place right now, what we are looking though is to replace it with a trigger with out violating MS Support policy. We decided to leave it as it is right now.

Thank you all for your posts.

Shas3