Generic database design to store logs of every database change

5 pts.
Tags:
Database
Database design
Hi there, Can anyone help me out by providing a generic database design for having logs of all the changes to my master table so that I can review all the changes at a later point of time through my application. Every response will be appreciated.... thanks, Sudhakar
ASKED: February 7, 2008  1:26 PM
UPDATED: February 8, 2008  7:22 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

What you want to monitor and capture, as well as your RDBMS will deturmin how you create your table or tables. These tables are written from the SQL Server point of view. You may need to adjust these for your RDBMS.

If you wanted to capture schema changes then a table something like this would be a good starting point.
CREATE TABLE SchemaMonitorObject
(LogId int IDENTITY(1,1) PRIMARY KEY,
ObjectName sysname,
ChangeTypeId int, /*1 Create Table, 2, Add Column, 3, Drop Column, 4 Drop Table*/
DateLogged datetime,
EnteredBy sysname,
ObjectType char(1));
CREATE TABLE SchemaMonitorColumn
(LogID int PRIMARY KEY,
ColumnName sysname);

If you needed to capture data changes then a table somethign like this would be a good starting point.
CREATE TABLE LogDataChange
(LogId bigint IDENTITY(-10000000, 1) PRIMARY KEY,
object_id bigint,
TableKeyId INT, /*This would be the unique identifier for the record of the source table which is being modified, typically the primary key from that table.*/
OldValue sql_variant, /*Using a trigger to capture the change the old value from the deleted virtual table. This will give you all the prior values in the log table, and have the current value in the table. We use the sql_variant data type so that all data types can be stored in a single column.*/
DateChanged datetime,
ChangedBy sysname,
WasRecordDeleted bit /*If the record was deleted this should be set to 1 so that it’s known that the record was deleted at this point.*/)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following