is possible to add a trigger in the table SysObject?

Question

is possible to add a trigger in the table SysObject?

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
January 19, 2005 at 11:18 am

#60385

Is possible to add a trigger in the table sysobject??

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I am sure that it is but the first question is WHY do you want to mess with one of the MOST IMPORTANT tables in your database like this????

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 2

Hi Aj Ahrens, well i will try to exponate that , excuse me my bad english,

Well i start tell you that in my informatic area are doing a migration from sqlserver 7 to sqlserver 2000 , happens that the log of transaction is off the bosses wanted that because they wish to get a best perfomance, so to indentify the changes about the objects i take a look into Sysobject, but if someone of my partners did some changes with an alter, the change doent register in the Sysobject, one friend of this forum said me that if occurs some alters, in the SysObject appears other entry and the field base_schema_ver changes, but i did a proof and only can see one entry into SysObject, although the field really changed, so i though an idea, it consists in create a trigger that it inserts into a log table when the field of SysObject is updated, but when i try to create the sql throwes an error messages.

That is my History i hope that you have understood me, sorry my bad english.

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

I would stick with AJ's point that you MUST NOT ALTER SYSTEM TABLES. I cannot stress this point enough. However what you could do that would have almost the same effect would be to do periodic checks of the system tables and check if something has changed..

EX:

--load a comparaison table

Select * into SysOSnapShot from dbo.SysObjects

then you can set-up a job that checks like every hour or day if the version of the object have changed.

You could audit if a new sp object has been created, or something deleted. You could also check if the version f the object is the same. You could audit all those changes in a reporting table that yourself and others could look up.

Once the audit is done. Truncate the table and reload the data from SysObjects and start all over again when the job is set-up to run.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 4

yes friend i though that too, but i want automatize the task and so i want to avoid to build some job to do that work, i prefer to use a trigger is more easy.

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

A job can be scheduled to run whenever you want just like a sceduled task in Windows... You'll have 1 more step to do and you're sure that nothing will get screwed up. I don't see that 5 minutes of work is worth risking losing the database... But that's just me.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 6

yes... it seems that there is no another way to do that ... thank you .)

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

I don't know of a way to do this in SQL Server 2000. Even if it would be possible, you wouldn't want to do so.

Yes, there are certain system tables you can define triggers on, but AFAIK, MS does not guarantee they will fire. Also, in doing so, you run an unsupported system. Imagine what MS support will say in case of a support call, when they see it. I wouldn't want to be in your shoes when you need to explain it to your boss

Maybe you should spend some money on a third party tool and/or implement a sound version management strategy.

--
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: 114642 More actions · Answer 8

Others have done some testing on this (Robert Marda) and have found they will not consistently fire. On some tables they will not fire at all.

K. Brian Kelley
@kbriankelley

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

I understand well the problem you're trying to solve. However, while throwing a trigger on top of a system table (even if it worked) only masks the problem, it doesn't solve it. The problem at hand is one of change control. You've got changes going in by multiple people and based on what you've said here, there's not a lot of coordination on these changes. While the trigger might have addressed telling you when something changes, it won't address the issues that having uncontrolled updates to schema creates: namely inconsistent and/or incompatible structures.

If it is at all possible, it's probably better to look at how you guys manage change in an environment and try and work out some coordination. If you don't, this problem will keep coming up. If it's not possible to attack the problem from a business/people perspective, then a scheduled job to do a comparison of the objects is probably your best bet. It won't solve the issues I named above, but at least you will be forewarned.

K. Brian Kelley
@kbriankelley