USEFUL SITES :
Write for Us
First of all, you should refrain from messing around with SQL Server's system tables, unless you are told to and are guided by Microsoft. You are running an unsupported system if you modify the system tables!
But if you don't want to listen to the above advice, why would one want to create a trigger on system tables?
The following two reasons seem to be among the most often asked about:
Creating a trigger on sysobjects in order to take some action when a new object is created or an existing object is modified.
Creating a trigger on sysusers or sysxlogins in order to take some action when a new user is added or already existing ones are about to be modified.
To be fair, the build-in support in SQL Server 2000 for these reasonable requirements is poor.
Having said that, it is technically possible to create trigger on system tables. Or at least to create trigger on some of the system tables. But they are not guaranteed to fire.
Now, what is meant with "on some of the system tables"? Well, to put it in easy words, there are two types of system tables: those that accept triggers, and those that don't. While:
obviously finishes successfully, but you will receive:
when trying to execute the following statement:
So, what can you do in such cases where you need to determine modifications on the system tables? Well, you can run a trace to capture these events. You can also activate SQL Server's build-in C2 auditing, which is likely to be an overkill, since it captures everything, use some third-party tools that offer such functionality, or simply wait for the next version with its' improved functionality in this area.
