Site sponsored by: Idera Try Idera’s new SQL admin toolset
SQL Server Performance

  • Home
  • Articles
  • Forums
  • Tips
  • FAQ's
  • Blogs
  • Software
  • Books
  • About Us
RSS Feeds
Sign in | Join


Article Topics

All Articles
Peformance Tuning
Audit
Business Intelligence
Clustering
Reporting Services
Developer
General DBA
ASP.NET / ADO.NET

SQL Server 2008 - Worth the Wait

SQL Server’s first significant upgrade in three years features a number of envelope-pushing enhancements and improvements. Which will have the greatest impact on SQL administration and development? More...
Latest Articles

Slowly Changing Dimensions in SQL Server 2005
Audit Data Modifications
SQL Server 2008’s Management Data Warehouse
Same Report but Different Methods in SQL Server Reporting Services ...

More     
 
Latest FAQ's

SSIS Lookups are Case Sensitive
Convert Number to Words in SSRS
After installing SP2 on SQL Server 2005 x64, when trying to ...
Remote Name Could not be Resolved in SQL Server Reporting Services ...

More     
   
Latest Software Reviews

SQL Server DBA Dashboard
SwisSQL DBChangeManager
SQLMesh - SQL Server Search Tool
SoftTreeTech SQL Assistant

More     

articles >> general dba >> Generate Log Files with a Trigger ...

Generate Log Files with a Trigger

By : Ken Kaufman
Oct 12, 2005
Printer friendly

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')


    Next Page>>    








Home | Peformance Articles | Audit Articles | Business Intelligence Articles | Clustering Articles | Developer Articles | Reporting Services Articles | DBA Articles | ASP.NET / ADO.NET Articles | DBA FAQ's | Developer Peformance FAQ's | DBA Peformance FAQ's | Developer FAQ's | Clustering FAQ's | Error Messages | Audit Tool Reviews | Backup Tool Reviews | Coding Tool Reviews | Compare Tool Reviews | Documentation Tool Reviews | Design Tool Reviews | Monitoring Tool Reviews | Log Tool Reviews | Reporting Tool Reviews | Clustering Tool Reviews | Security Tool Reviews | Change Management Tool Reviews | Remote Access Tool Reviews | Book Reviews | Security Tool Reviews | QDPMA Performance Tuning | ADO.NET / ASP.NET | Administration | Analysis/OLAP Services | Application Development | Configuration | Components | ETL | Hardware | High Availability | Hints | Index | Misc | Operating Systems | Performance Tuning | Replication | T-SQL | Views