Tracking changes to a database via triggers and the transaction logs

Question

Post reply

Tracking changes to a database via triggers and the transaction logs

kaplan71

Hall of Fame

Points: 3634
More actions
March 10, 2013 at 5:33 pm

#269404

Hello --
We have SQL Server 2008R2 standard running on one of our servers. The server contains three databases. One of the databases contains data that normally does not change, and is of such nature that if a change occurs, the administrators should be notified of the event. The databases on the server all part of a maintenance plan that includes Full, Differential, and Transaction Log backups, the last of which is backed up once every hour. The Database Mail utility has been configured so that notifications of job failures are sent to the administrators.
A colleague of mine suggested that a trigger could be created that would monitor the transaction logs for entries that recorded any changes in the database. This trigger could then send a notification to the administrators. I had several questions concerning this approach.
1. Is this the correct approach?
2. If the answer to the first question is yes, is this something that can be done through the SQL Studio?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Have you examined / evaluated the use of Change Data Tracking. If not you may want to start here"

http://msdn.microsoft.com/en-us/library/bb933875(v=sql.105).aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

andrecesarr SSC Veteran Points: 207 More actions · Answer 2

Hi!

I wrote a simple script one time that can help you on this question:

/*

TRACKING DML COMMANDS (INSERT,UPDATE,DELETE)

BY ANDRÉ CÉSAR RODRIGUES 13/09/2012 V1

*/

--FIRST YOU NEED TO CREATE IN SOME DATABASE THE TABLE BELOW THAT WILL KEEP THE TRACKING DATA

CREATE TABLE DMLMon(Changed TIMESTAMP,DateChanged DateTime,TableName char(30),UserName varchar(50),AppName varchar(50),host_name varchar(50),Operation char(6))

--DROP TABLE DMLMon

--CHANGE THE NAME TbMon FOR THE NAME OF THE TABLE YOU WANT TO MONITORING.

CREATE TRIGGER AuditDML ON TbMon

AFTER

INSERT, UPDATE, DELETE

AS DECLARE @Operation char(6)

if exists (select * from inserted) and exists (select * from deleted)

select @Operation = 'Udate'

else if exists (select * from inserted)

select @Operation = 'Insert'

else

select @Operation = 'Delete'

INSERT INTO DATABASE.dbo.DMLMon(DateChanged,TableName,UserName,AppName,host_name,Operation)

SELECT GetDate(), 'TbAuditada', suser_sname(),app_name(),host_name(),@Operation

-- Change the DATABASE to the name of the database that you create the table in the beggining of this script.

/*

Good luck!

Regards,

André CR

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

kaplan71 (3/10/2013)
A colleague of mine suggested that a trigger could be created that would monitor the transaction logs for entries that recorded any changes in the database. This trigger could then send a notification to the administrators. I had several questions concerning this approach.
1. Is this the correct approach?
2. If the answer to the first question is yes, is this something that can be done through the SQL Studio?

Well, first problem there is that the transaction log is not a table and can't have a trigger put on it. Second problem is that the transaction log is not an audit log, it's for database integrity and durability.

Have a look at Change Data Capture and SQLAudit

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass