Using triggers to generate data manipulation logs for database tables

As a database administrator, you try to provide departments with the data they need, in different formats that are well known to each department. You usually submit data in MS Excel format to the accounting department, or the data is presented as an HTML report to a regular user. Your system security administrators are accustomed to using a text reader or Event Viewer to view the log. This article describes how to use triggers to record changes to a particular datasheet in a database using DML (data manipulation language). Note: The following example is an INSERT type trigger, but it is also easy to change to a delete/update type trigger.

Procedure first let's create a simple table in the Northwind database.

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

Once you have created this datasheet, add a standard message to the sysmessages datasheet in the master database. Notice that what I'm adding is a parameter variable to accept a character value that will be displayed to the administrator. By setting the @_with_log parameter to true, we guarantee that the relevant results are sent to the event log.

sp_addmessage 50005, 10, '%s', @with_log = true

Now we create this message populated with meaningful information. The following information populates this message and is logged to the file:

• Type of operation (insert).

• Data tables that are affected.

• Date and time of change.

All the fields that were inserted by the statement. The following trigger creates a string with predefined values (1~3 characters) that are in the inserted datasheet. (This inserted data table resides in memory, which holds the row of records that are inserted into the data table where the trigger is located). Triggers connect these values and place them in a @msg variable. The variable is then passed to the RAISERROR function, which writes it to the event log.

Create trigger TestTrigger on
tablefortrigger
for insert
as

--Declaring variables that store messages

Declare @Msg varchar (8000)

--Assign the action/table name/Date time/Insert field to the message

set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | '
+(select convert(varchar(5), track)
+ ', ' + lastname + ', ' + firstname
from inserted)

--An error has been sent to the Event Viewer.

RAISERROR (50005, 1, @Msg)

Run the following statement to test the trigger, and then 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 way to write to the event log, let's modify the trigger to write the data into a text file. This change also requires adding another variable @cmdstring, and using the extended storage process xp_cmdshell.

Because we're writing to the file system, security permissions start to have an impact. Therefore, the user performing the insert operation must have read and write access to the text file. Therefore, the design of a C/s structure of the application for multi-user operation, may not be a feasible solution. A more reasonable scenario is to design a three-tier application that calls your middle-tier component on a Single-user database. In the latter scenario, the rights management of that text file is actually easier than managing a user.

Alter trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)

--Storage of orders to be executed by xp_cmdshell

Declare @CmdString varchar (2000)
set @_msg = ' insert | tablefortrigger | ' + convert ( varchar ( 20 ) , getdate ( ) ) + ' | ' + ( select convert ( varchar ( 5 ) , track ) + ' , ' + lastname + ' , ' + firstname from insert ) -
[99%]set @Msg = 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | ' +(select convert(varchar(5), track) + ', ' + lastname + ', ' + firstname from inserted)

--An error has been sent to the Event Viewer.

raiserror( 50005, 10, 1, @Msg)
set @CmdString = 'echo ' + @Msg + ' >> C:\logtest.log'

--Write to a text file

EXEC Master.dbo.xp_cmdshell @CmdString

Let's test it, run the previous INSERT statement, and then open the C:\logtest.log file to see the results:

Insert into Tablefortrigger (LastName, FirstName) Values (' Doe ', ' John ')