The virtual tables which can be accessed via a trigger are called inserted and deleted. The inserted table contains the new values, while the deleted table contains the old values.
Logging the security events won’t help you see what commands are being run against the database server. It will only log when someone logs into the database, not what they do.
Fire up SQL Server Profiler (Start > Programs > Microsoft SQL Server 7 > SQL Server Profiler). This will allow you to see all the commands which are running, as well as what application is calling them.
When you create a new trace the events you are going to be most interested in are:
They should be selected by default. You can remove the other events which are there by default. You can then leave SQL Profiler running for a couple of days and get a full view of everything that is happening. If you have a high load SQL Server what ever machine you run SQL Profiler on will need a lot of disk space. It’s recommended that you don’t run SQL Profiler on the SQL Server it self as SQL Profiler is a very system intensive application which can cause performance problems when running it on the servers console. I typically run SQL Profiler from my workstation against our production databases.