InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 02:02:14 GMT20http://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxHiTable will only will be used for audit purpose so we could fingerpoint or otherwise know who did what. To undo unwanted changes we are already following data replication and data backup. So that is not much a issue.We can't use bigint instead of varhar for primary field as not every table will have primary key as identity. Instead we have quite a lot of tables with primary key as varchar.As per our biz policy one table could have only one column as primary key. If more than one column is required, then we have to divide that table into parts and then create a master table to map these sub tablesMon, 06 Jun 2011 23:34:04 GMTaavinash9http://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxok...i see the plan...but it goes back again to what will the table be used for? the purpose of the table has a lot to do with the best way to tackle this...there's lots of built in options, but can you describe what you'd d0 with he data in the table after it exists?is it just an audit(so you could use a trace instead?) so you can fingerpoint or otherwise know who did what?is it to be able to undo unwanted changes?is it to satisfy a SOX requirement or something? but what about the following?what about a table that doesn't have a single column for it's primary key? admittedly, depending on your biz, you might always have a single column PK, but i know i have quite a few that do not. if you are consistent, and every table's PK happens to be an identity, you could use bigint instead of varchar(1000) for the definition.what happens when, say 5 columns in a table were changed? you want 5 rows in the audit table added? very hard to write a script to undo that, because since the value is in a nvarchar(max0, you have to custom write the script , with CONVERT commands sometimes, and merge those 5 rows back into a single one, for all the rows affected.Mon, 06 Jun 2011 23:09:09 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxHi, Let me make myself clear. I have a table with below table structure. It will just maintain the information that on which table with what primary(with its value) does the change occurred on what type(insert, update, delete) with old value of the column on which change occur and the new value of the column with added information about who made the changes and at what time?Table Structure:CREATE TABLE [dbo].[DataAudit]( [Pk_RowID] [int] IDENTITY(1,1) NOT NULL, [AuditID] [int] NOT NULL, [Type] [nchar](1) NULL, [TableName] [nvarchar](128) NULL, [PrimaryKeyField] [nvarchar](1000) NULL, [PrimaryKeyValue] [nvarchar](1000) NULL, [FieldName] [varchar](128) NULL, [OldValue] [varchar](max) NULL, [NewValue] [varchar](max) NULL, [UsrID] [varchar](20) NULL, [Udt] [datetime] NULL, [BusinessDt] [datetime] NULL, [ConcurrencyID] [timestamp] NOT NULL,PRIMARY KEY CLUSTERED ( [AuditID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Mon, 06 Jun 2011 22:52:44 GMTaavinash9http://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxa single, master table tracking all changes and the original values isn't really going to work well...think it through with me.\if you want to keep old and new values, you also need to keep their datatype, right?so your single, master tables going to need to either put everything in nVarchar columns, or have a suite of different columns,one for each data type, so you can lnow that the column FirstName changed from 'Bob' to Robert, but also be able to track that the value Total Amount changed from 100 to 101.that type of tables pretty difficult to work with, and gets a little unmanageable when you actually need to reverse a transaction out of it.CDC puts the changes into a parallel table under the cdc schema, so changes to dbo.Invoices can be found in the mirrored table cdc.Invoicesit's very easy to query those tables in that case, and since they are tables, you could build a VIEW on those tables that could emulate the original structure you envisioned for the master table idea, but with all the advantages of CDC.you mentioned robust and flexible, but what is the purpose....to recover/undo changes if needed, or to identify who made a change and when?Mon, 06 Jun 2011 22:37:52 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxHi, I read about CDC. What information i got is that CDC creates a change table structure in the log for each source table and then for every data change in tables is recorded in this meta tables. Thus though CDC is good for data warehousing, it's will not work in my case, as I require only one table in database which will maintain information about change in data in every tables in that data base. Anyway thanks for the your help. It was truly appreciated. Mon, 06 Jun 2011 22:29:40 GMTaavinash9http://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxsure![b][url=http://www.lmgtfy.com/?q=SQL2008+Change+Data+Capture] more information about SQL2008 Change Data Capture[/url][/b]Mon, 06 Jun 2011 22:26:52 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxThanks.Can you provide more information about it?Mon, 06 Jun 2011 22:08:50 GMTaavinash9http://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxSQL2008 introduced CDC...Change Data Capture...which is going to do everything you are asking and more, it's built in and it's documented, working and can be implemented on each table you need to audit with a couple of lines of code.I'd recommend that before anything else.Mon, 06 Jun 2011 07:49:27 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1120264-1281-1.aspxHi, I am new to SQL service broker and I don't know anything about. I have to develop an application for my company such that each and every transaction, a entry will be made in audit table for the old value and new value. Our company has many database(though size each one is less). I have to maintain all transaction that is happening in each database in a master database plus each one of the database. Can someone provide me with step by step process that i have to follow to make a robust and flexible application as soon as possible. Any help will be welcome. Thanks in advanceMon, 06 Jun 2011 07:38:20 GMTaavinash9