Solved

Tracking data changes

Posted on 2010-09-07
7
365 Views
Last Modified: 2012-05-10
Dear experts,

I need to track all data changes in certain tables of my database and store information about the changes in a separate table, changelog.
I guess I should do it using triggers, but maybe you could tell how the trigger that could do that should look?
The changelog table has the following fields: Table (that holds table name where the change happened), Field (where the data was changed), Key (of the modified record), OrigValue, NewValue, UpdateTime, UserName.

Thanks.
0
Comment
Question by:tantormedia
  • 4
  • 2
7 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33618757
this link has all the info you need. Scroll all the way down.

http://www.sqlservercentral.com/Forums/Topic294216-314-1.aspx#bm299093

0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33618851
This is the complete working solution you can modify for your needs.

If you get hung, please post back where you need help.

CREATE TABLE [dbo].[Audit](
[AuditId] [bigint] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserId] [int] NULL,
[SystemUser] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Operation] AS (case when [OldValue] IS NULL then 'Insert' else case when [NewValue] IS NULL then 'Delete' else 'Update' end end),
[OldValue] [xml] NULL,
[NewValue] [xml] NULL,
[OldBinaryValue] [varbinary](max) NULL,
[NewBinaryValue] [varbinary](max) NULL
)


CREATE TABLE [dbo].[AuditTable](
[TableName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IdentityColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserIdColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_AuditTable] PRIMARY KEY CLUSTERED
(
[TableName] ASC
)


create proc [dbo].[SaveAudit]
@OldValues xml,
@NewValues xml

as

Declare @TableName varchar(128), @SystemUser varchar(80), @UserId int,
@ColumnName varchar(128), @Operation varchar(10), @sql varchar(1000)

SELECT @SystemUser=system_user

SELECT @TableName=object_name(resource_associated_entity_id)
FROM sys.dm_tran_locks
WHERE request_session_id = @@spid and resource_type = 'OBJECT'

Insert Into Audit (TableName, UserId, SystemUser, OldValue, NewValue)
Values (@TableName, null, @SystemUser, @OldValues, @NewValues)
go


CREATE proc [dbo].[GetAuditInfo]
@TableName varchar(128),
@StartDate datetime,
@EndDate datetime
as

set nocount on

Declare @x int, @max int, @sql varchar(4000), @ColumnName varchar(200)
set @x=1
set @sql=''

SELECT @max=count(c.[name])
FROM sys.all_columns c
INNER JOIN sys.all_objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.[name] = @TableName AND o.[type] = 'U'

while (@x <= @max)
Begin

SELECT @ColumnName=c.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o ON c.object_id = o.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.[name] = @TableName and c.column_id=@x AND o.[type] = 'U'

set @sql=@sql+'v.value(''@'+@ColumnName+''', ''varchar(8000)'') as '+@ColumnName
if @x<@max
set @sql=@sql+', '

set @x=@x+1
End

Declare @sql2 varchar(8000)

set @sql2='SELECT TableName, DateCreated, SystemUser, Operation,'+@sql+
' Into ##340834audit FROM Audit CROSS APPLY OldValue.nodes(''/deleted'') x(v)
Where TableName='''+@TableName+''''

exec(@sql2)
set @sql2=''
set @sql2='Insert Into ##340834audit SELECT TableName, DateCreated, SystemUser, Operation,'+@sql+
' FROM Audit CROSS APPLY NewValue.nodes(''/inserted'') x(v)
Where TableName='''+@TableName+''''

exec(@sql2)

select * from ##340834audit Order By TableName, DateCreated, SystemUser, Operation
drop table ##340834audit
go


And finally.....a generic audit trigger for your tables

Create trigger [dbo].[AuditYourTable] on [dbo].[YourTable]
for insert, update, delete
as
declare @i xml, @d xml
set @i = (select * from inserted for xml auto)
set @d = (select * from deleted for xml auto)
exec SaveAudit @d, @i
GO
0
 

Author Comment

by:tantormedia
ID: 33619048
sammySeltzer,

Thank you very much. But is it really MySql? Looks like MS SQL Server syntax to me.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33619276
Oops, my bad.

I am sorry I thought it was sql server.

Sorry about that.

I am not too good with MySQL but let me see what i can do.
0
 
LVL 8

Accepted Solution

by:
wolfgang_93 earned 250 total points
ID: 33620169
In a default installation of MySQL, all commands are logged to a file (in the case of a
Linux install, the file has a name like .mysql_history).

Rather than set up a trigger -- which will cause a lot of extra activity in the database --
you could instead write a little script to periodically read and parse the contents of the
file and load information of interest to a MySQL table.

0
 
LVL 28

Assisted Solution

by:sammySeltzer
sammySeltzer earned 250 total points
ID: 33620613
However, should decide is still your choice, here is information I googled up that looks closer to what you are looking for.

I have also included the link so you can understand what they did and *how* they did it.

Hope this helps you.

http://codespatter.com/2008/05/06/how-to-use-triggers-to-track-changes-in-mysql/


DROP TRIGGER IF EXISTS history_trigger $$
 
CREATE TRIGGER history_trigger
BEFORE UPDATE ON clients
    FOR EACH ROW
    BEGIN
        IF OLD.first_name != NEW.first_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'first_name',
                        NEW.first_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;
 
        IF OLD.last_name != NEW.last_name
        THEN
                INSERT INTO history_clients
                    (
                        client_id    ,
                        col          ,
                        value        ,
                        user_id      ,
                        edit_time
                    )
                    VALUES
                    (
                        NEW.client_id,
                        'last_name',
                        NEW.last_name,
                        NEW.editor_id,
                        NEW.last_mod
                    );
        END IF;
 
    END;
$$
0
 

Author Closing Comment

by:tantormedia
ID: 33627101
Thanks.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now