I'm .NET developer not a database developer. But the customer I'm with since June this year wants to have as many things on the database as possible. Yes, as many THINGS as possible. First I thought why would you want to, but now I start getting it. The customer has a long history of Oracle application and as I understood everything is done in the database in case of an Oracle application.

Most of the time I would use a database as a data-store. Using it for creating, reading, updating and deleting data. If the client requires the usage of Stored Procedures, I basically create an Insert, Update and Delete Stored Procedure for every table, nothing special. For the selection of data I create a select by id, and selects that are required by the functionality for the specific table.

But of course the database has a lot of more power. The customer wanted to have a journal of all data operations. It is possible to add this functionality through application code that registers the calls in separate tables. It is also possible to add this functionality through more intelligent Stored Procedures. But the way the DBA wants it to have is the best solution I think. It is through triggers. As my database knowledge is pretty basic, I had to learn how to tackle this functionality using triggers. Now I've set up this functionality I must admit this solution is the only appropriate solution. The solution is tested, and if I'm adding a new stored procedure change my application or whatever change, I don't have to change my journal triggers (if I'm not changing the table structure of course).

The journal table must consist of the following structure: operation type, operation date, user and for every column a new_ and a old_ column.

It's important to understand that triggers are set-based in SQL Server 2000. At first people start testing using a single row, but when you for example update every record from a specific table only one trigger gets fired. Inside the trigger you can get the data using the virtual tables INSERTED and DELETED. For an Insert only the INSERTED columns are provided. When updating a set of data both the INSERTED and DELETED tables are filled. And on deletion only the DELETED table is filled. You can relate the INSERTED and DELETED data by joining them on the primary-key.

This solution makes sure that every data operation get's journaled, not only when calling Stored Procedures or when accessing the application.

Triggers are one of the things I've learned to appreciate of the non-basic dbms functionality. While I'm still no database guy, more on databases will follow in the near future.