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:
use msdb go create trigger test on dbo.backupfile for insert, update, delete as select ‘Hallo Welt’ drop trigger test The command(s) completed successfully.obviously finishes successfully, but you will receive:
Server: Msg 229, Level 14, State 5, Procedure test, Line 4 CREATE TRIGGER permission denied on object ‘sysobjects’, database ‘master’, owner ‘dbo’.when trying to execute the following statement:
use master go create trigger test on dbo.sysobjects for insert, update, delete as select ‘Hallo Welt’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.
]]>