SQL Server trigger

[wasiq]

Is there a way to log each and every transaction that took place using some form of a trigger at the database level. I am using sql server 2000.

[lou_davis]

Where do you want to log transactions to and what information are you looking to log?

[wasiq]

The log can be anywhere, either a file, or maybe a table. And I want to log all the sotred procedures that are being called by all users.

[lou_davis]

Okay, that's pretty easy. Since you want to log stored procedure execution and not transactions to the table, you could create a 'utility' stored procedure that can be called from all the stored procedures you wish to log. This would simply be a line added to your existing stored procedure. A trigger would not fit your scenario since they are designed to be used with tables only.

This stored procedure could accept the sp name being executed as an input parameter. To determine the user executing it, use CURRENT_USER function. Refer to SQL Books On Line if you are not familiar with this to learn more about it.

From this point, you can then insert this information as well as Date/Time and other other information you wish to track into a custom table for later use. One word of caution though. If there are a lot of users executing these stored procedures that are going to be tracked, this table can become quite large pretty quickly.

Looking at your original post, it looks like you do wish to track transactions. If so, I would recommend, if possible, changes to your table structures to add some columns. These columns could be CreateDate, CreatedBy, UpDated(dateTime), and UpdatedBy.

With these new columns, as rows are inserted or updated, you can add the information of when rows were created and updated and by whom. An additional column of UpdateCount that increments after each update would also let you know how many times a row in a table was updated. Again triggers would not be necessary in this scenario. You would only have to modify your existing Insert and Update statements to include this information.

Hope this helps. Good Luck

[Mongo]

Using a trace (or in particular a blackbox trace) may give you what you
need. Do a search of BOL and online to see how easy it is to do.

[wasiq]

Thanks a lot