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

Let's start by creating a simple table in the Northwind database.

create table tablefortrigger
(
track int identity(1,1) primary key,
Lastname varchar(25),
Firstname varchar(25)
)

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.

Create trigger TestTrigger on
tablefortrigger
for insert
as
--Declare variable to hold message
Declare @Msg varchar(8000)
--Assign to message "Action/Table Name/Time Date/Fields inserted
set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | '
+(select convert(varchar(5), track)
+ ', ' + lastname + ', ' + firstname
from inserted)
--Raise Error to send to Event Viewer
raiserror( 50005, 10, 1, @Msg)

Test it by running the following statement, and view the event log.

Insert into tablefortrigger(lastname, firstname)
Values('Doe', 'John')

If you open the event log you should see the following message:

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.

Alter trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)
--Will hold the command to be executed by xp_cmdshell
Declare @CmdString varchar (2000)
set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' — '
+(select convert(varchar(5), track)
+ ', ' + lastname + ', ' + firstname
from inserted)
--Raise Error to send to Event Viewer
raiserror( 50005, 10, 1, @Msg)
set @CmdString = 'echo ' + @Msg + ' >> C:\logtest.log'
--write to text file
exec master.dbo.xp_cmdshell @CmdString

Let's test it by running the previous insert statement. When completed, open the C:\logtest.log file to view the results.

Insert into tablefortrigger(lastname, firstname)
Values('Doe', 'John')