Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Tracking data changes

Posted on 2010-09-07
7
368 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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