tantormedia
asked on
Tracking data changes
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.
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.
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(res ource_asso ciated_ent ity_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(''@'+@C olumnName+ ''', ''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
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_
[DateCreated] [datetime] NULL DEFAULT (getdate()),
[UserId] [int] NULL,
[SystemUser] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_
[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_
[IdentityColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[UserIdColumnName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_
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(res
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(''@'+@C
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'
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
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
ASKER
sammySeltzer,
Thank you very much. But is it really MySql? Looks like MS SQL Server syntax to me.
Thank you very much. But is it really MySql? Looks like MS SQL Server syntax to me.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
http://www.sqlservercentral.com/Forums/Topic294216-314-1.aspx#bm299093