How to design database to store userlog of changing data?
-
Sorry for a stupid question. I am desgning a new DB in SQL server.In this i have a table for example TBLData1 that contain 10 field.and a table TBLUser. User login from my ASP website can change the data in the table TBLData1.And I Want to record all the log in order to know exactly who has change anything and when this action is happen. In my opition I create another table TBLData1_Log it have ten field same in TBLData1 and some more field like this: ID_USer. Date_Change. Reason_To_Change. .......... Any time user change the data at the table TBLData1. It will store the old value to TBLData1_Log and added some more field. I am wondering if this design is ok? Because the more table, the more log table in my Design. Please help me to design better. THanks.
-
Sorry for a stupid question. I am desgning a new DB in SQL server.In this i have a table for example TBLData1 that contain 10 field.and a table TBLUser. User login from my ASP website can change the data in the table TBLData1.And I Want to record all the log in order to know exactly who has change anything and when this action is happen. In my opition I create another table TBLData1_Log it have ten field same in TBLData1 and some more field like this: ID_USer. Date_Change. Reason_To_Change. .......... Any time user change the data at the table TBLData1. It will store the old value to TBLData1_Log and added some more field. I am wondering if this design is ok? Because the more table, the more log table in my Design. Please help me to design better. THanks.
Why would you duplicate all the data from one table in the log table? Use key values, or just save the userid (or whatever you use as identifier) and the transaction details. Then when you want to print a report you can lookup the user's details by the identifier.
-
Sorry for a stupid question. I am desgning a new DB in SQL server.In this i have a table for example TBLData1 that contain 10 field.and a table TBLUser. User login from my ASP website can change the data in the table TBLData1.And I Want to record all the log in order to know exactly who has change anything and when this action is happen. In my opition I create another table TBLData1_Log it have ten field same in TBLData1 and some more field like this: ID_USer. Date_Change. Reason_To_Change. .......... Any time user change the data at the table TBLData1. It will store the old value to TBLData1_Log and added some more field. I am wondering if this design is ok? Because the more table, the more log table in my Design. Please help me to design better. THanks.
We use triggers spit to populate the following table. IMHO this is the only valid use of triggers spit. This also services all tables that are being audited.
CREATE TABLE [dbo].[AuditLog](
[AuditID] [INT] IDENTITY(1,1) NOT NULL,
[Action] [CHAR](1) NULL,
[TableName] [VARCHAR](128) NULL,
[PrimaryKeyField] [VARCHAR](1000) NULL,
[PrimaryKeyValue] [VARCHAR](1000) NULL,
[FieldName] [VARCHAR](500) NULL,
[OldValue] [VARCHAR](8000) NULL,
[NewValue] [VARCHAR](8000) NULL,
[ModifiedDate] [DATETIME] NULL DEFAULT (GETDATE()),
[UserName] [VARCHAR](200) NULL
) ON [PRIMARY]Actions being Add,Edit,Delete
Never underestimate the power of human stupidity RAH
-
Sorry for a stupid question. I am desgning a new DB in SQL server.In this i have a table for example TBLData1 that contain 10 field.and a table TBLUser. User login from my ASP website can change the data in the table TBLData1.And I Want to record all the log in order to know exactly who has change anything and when this action is happen. In my opition I create another table TBLData1_Log it have ten field same in TBLData1 and some more field like this: ID_USer. Date_Change. Reason_To_Change. .......... Any time user change the data at the table TBLData1. It will store the old value to TBLData1_Log and added some more field. I am wondering if this design is ok? Because the more table, the more log table in my Design. Please help me to design better. THanks.
In order to maintain a history of changes to the database's data we need to record every insert, update, and delete to some sort of "history" table. In addition to capturing the data that was inserted, updated, or deleted, we also need to note what user made the modification, as well as the date and time it was made. Furthermore, there are likely lookup tables or other ancillary tables whose history of changes do not need to be tracked. Therefore, we need some way to indicate those tables that need their changes tracked.
The two main challenges in creating an audit log of database changes is determining what mechanism will be used to log changes and how the history of changes will be persisted to the database. The remainder of this article looks at different techniques for persisting the history of changes to the database and their pros and cons. Maintaining a Log of Database Changes explores techniques for logging data modifications to the " history & quot tables. -
In order to maintain a history of changes to the database's data we need to record every insert, update, and delete to some sort of "history" table. In addition to capturing the data that was inserted, updated, or deleted, we also need to note what user made the modification, as well as the date and time it was made. Furthermore, there are likely lookup tables or other ancillary tables whose history of changes do not need to be tracked. Therefore, we need some way to indicate those tables that need their changes tracked.
The two main challenges in creating an audit log of database changes is determining what mechanism will be used to log changes and how the history of changes will be persisted to the database. The remainder of this article looks at different techniques for persisting the history of changes to the database and their pros and cons. Maintaining a Log of Database Changes explores techniques for logging data modifications to the " history & quot tables.