Yes, I think triggers on system tables can be useful in some cases. At the very least Microsoft could provide some triggers in an inactive form that could be activated should a DBA want them. The reason I tried to put a trigger on sysprocesses is because I developed a way to detect deadlocks and save the query of both processes involved in the deadlock. This would eliminate the need to review profiler data and to monitor for a certain period of time until a deadlock appeared.

Even though I am unlikely to use these techniques, I do believe that used correctly and with caution they would not cause an adverse effect on a SQL Server. Take, for example the first technique described. SQL Server sets this flag every time you set up replication, so the technique is not doing something that should never be done it is simply doing it without setting up replication. If you try to drop a replicated table you will get the same error as when you use the technique I described in example 1.

Also, these techniques can be used to avoid accidents. I think we have all made mistakes on production SQL Servers at one time or another.

The placing of triggers on system tables should only be done as experiments since this is something that SQL Server does not do naturally under any conditions and so they will be very risky at this point. Who knows, if I'm lucky someone from Microsoft will read this article and consider its implications and allow those who want triggers on system triggers to put them there.

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.