Tracking changes to a database via triggers and the transaction logs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Tracking changes to a database via triggers...

Tracking changes to a database via triggers and the transaction logs

Rate Topic

Display Mode

Topic Options

Author

Message

kaplan71

Posted Sunday, March 10, 2013 5:33 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 2:11 PM
Points: 31, Visits: 74

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?

Post #1429035

bitbucket-25253

Posted Sunday, March 10, 2013 6:08 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:13 PM
Points: 5,103, Visits: 20,214

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
Before posting a performance problem please read

Post #1429041

andrecesarr

Posted Monday, March 11, 2013 7:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 6:11 PM
Points: 9, Visits: 40

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

Post #1429247

GilaMonster

Posted Monday, March 11, 2013 7:16 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 37,741, Visits: 30,020

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 2008, MVP
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

Post #1429250

« Prev Topic | Next Topic »

Permissions