SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracking changes to a database via triggers and the transaction logs


Tracking changes to a database via triggers and the transaction logs

Author
Message
kaplan71
kaplan71
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 352
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?
bitbucket-25253
bitbucket-25253
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24253 Visits: 25280
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
andrecesarr
andrecesarr
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 60
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370175 Visits: 46951
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search