Generate Log Files with a Trigger

As a DBA you try to supply data in a format that is familiar to the audience you’re presenting it to. Many times, you’ll present data in MS Excel to the accounting department, or an HTML report to a standard user. Your system and security administrators are no different; they are used to reading logs as running text or in the event viewer. The following article will show you how to log DML changes to specific tables in your database, using triggers. Note: The following examples work with an Insert statement, but can be modified easily for Delete or Update.

Steps

Once the table is created, let’s add a standard message to the sysmessages table of the master database. Take note that all I’m adding is a parameter to except a character value, which will be the output seen by your administrators. By setting the “@with_log” parameter to true, we’re assuring that the results are sent to the event logs.

sp_addmessage 50005, 10, ‘%s’, @with_log = true

Now that we created the message we’ll need to populate it with meaningful information. The following information will populate this message, and log the file.

Type of action (Inserted).

Table Effected.

Date and Time of Change.

All the fields inserted by the statement.

The trigger below creates a character string using predefined values, (1-3 above), and the values located in the “inserted” table. (The inserted table is a memory resident table that holds the row/rows inserted into the table you have placed your trigger on). It concatenates these values and places them into a variable called @msg. This variable is then passed to the “raiserror” function that writes it to the event log.

Now that we have a method of writing to the event log, let’s write data to a text file by altering the initial trigger. The changes made include adding another variable @CmdString and using the xp_cmdshell extended stored procedure.

Since we will be writing to the file system, security permissions come into play. Therefore, the user executing the insert must have security access to write to the text file. With this in mind, this is probably not a viable solution for multiple users running in a client/server application. It is more appropriate for a three-tier application, where your middle tier component is making the calls to the database under a single user. In the latter case, the management of permissions on the text file is easier to administer as it is only managing one user.