March 19, 2005 at 9:47 pm
I need a way to get the effect of insert and update triggers on system tables (sysobjects and syscolumns). This is on a SQL 2000 database and of course it does not want to let me put a trigger on a system table. I have even tried turning on 'allow updates' and then forcing a trigger into the appropriate tables however it does not appear to fire.
I have also tried creating a material view but again SQL does not let me materilize a view on a system table.
I really just need to take action when schema changes occur in a database.
Any ideas would be greatly appreciated.
Jim
March 20, 2005 at 4:11 pm
Triggers on objects aren't supported because of the behavior you've seen. Robert Marda did a lot of experimentation a few years back and he found that there were certain system tables that would never fire, just as you did.
You can set up a trace either through Profiler or the requisite stored procedures within SQL Server. You can monitor for the Object:Created event. As long as you have the ObjectName or ObjectID data column, you'll be able to identify what objects are being created/dropped. You can also monitor for the ALTER keyword in the T-SQL if you import the trace into a trace table. This will give you reporting after the fact, but at least you'll know who made a change and why. If nothing else, this will give you the data you need for more stringent change control procedures in your organization.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply