Reader to Reader - SQL Server - 01 Oct 1999

In a relational database management system (RDBMS) such as SQL Server, a log of users' transactional activity in the database is important, but having the log show detail to the level of when a user created or modified a single record is fundamental. This level of detail in a transaction log helps you solve many mysteries about modifications to a record. The log lets you know which user last inserted or modified a record. For example, in tracking database activity, the DBA needs to know the exact moment when a particular user created or modified a record. This capability can be important for recording evidence of incorrect application use. Also, tracking how many inserts and updates a user makes helps the DBA make decisions about performance-tuning the database and the server (e.g., setting up replication to optimize access and reply times).

No automatic trace log for a user's transactional activity in a table or record exists, but you can use the code in Listing 1 to create one. When I create a database schema, I always include four fields at the end of each table: SZINS, DTINS, SZMOD, and DTMOD. These fields contain, respectively, the name of the user who inserted the record, the insert date, the user who modified the record, and the date of the modification. But how do you automate inserting data into these four fields? To add the user and date to the first two fields, use the command DEFAULT when you create the table. Adding the necessary data to the last two fields, SZMOD and DTMOD, is a little more complicated. Each UPDATE statement in your application needs to place the name of the user who modified the record and the date in the SZMOD and DTMOD fields. Remembering to insert data in the fields with each update is difficult, and if you forget, some records might have incorrect or missing data. A better solution is for each update to activate a trigger that reports the user who modified the record and the modification date in the fields SZMOD and DTMOD. Listing 1 shows the code with the CREATE TRIGGER command to do so.

In Listing 2, the UPDATE command updates the SZMOD and DTMOD fields to contain the name of the user who made the update and the date and time of the modification. You can see in Figure 1, which shows the result of Listing 2, that SZMOD and DTMOD contain the name of the user, date, and time of the modification, which are placed in the record when the trigger executes and automatically stores the data.

Keep in mind that you need to disable recursion for the trigger. Otherwise, undesirable recursion occurs for each UPDATE statement. By default, the trigger recursion is set to False, but for security, you need to execute the script in Listing 3.

Listing 4 shows how you can up-date the fields SZMOD and DTMOD with the same values as the SZINS and DTINS fields, as Figure 2 shows. You can use this query's results to find out how many records are never updated by an ad hoc query. And by looking for records that have values in the SZMOD and DTMOD fields that differ from the values in SZINS and DTINS, you can track the number of record updates.

You can automatically attach any information in a record in these fields. For example, I put the name of the user's PC in the fields SZINS and SZMOD to differentiate users who have the same login name. In a case where you want to report the user name, you can substitute user_name for host_name in the UPDATE statement in Listing 1.

Creating a user activity log that shows the level of detail in these examples is a great way to track transactional activity. In your role as a DBA, a user activity log can help you when you need user activity information for decision-making.