Auditing Alter Table

Hi,

As per your replies for the post titled "Audited Table Schema Column Addition Change" id I alter table to add one column and after that If I want to include the same column into the audit table then the tool will drop the existing table and recreate the table with new columns. So all the previous audited data would be lost? Is their any way to save the previous data.

How to use the API's? Is their any trial version for the same?

In recent versions before dropping the old table, DB Audit Management Console displays a prompt offering to move the old data to the new audit table so that you can keep the old audit trail data and new one in one place. That prompt is not displayed in case the structures of the old and new tables cannot be automatically matched and it doesn't know how to move the data from the old table. In such case, you should get a prompt offering to export data to an external file so that you can import it manually later

Hi,

In the reply I see that there is an option to migrate data from the old to the new table but there are some limitations. Exactly what are the limitations for "structures of the old and new tables cannot be automatically matched". What is the matching criteria?

Thanks,

Hi,

It's quite simple. If you add new columns to the audited table, the old data can be migrated automatically, because the table structure is obviously extended. If you rename columns, change data types, remove columns from the audited table, the data cannot be migrated automatically, you would need to do that manually if you want to preserve it.

Hope that helps

Hi,

I tried what your mentioned in the previous reply. But I am getting the exact opposite result of what you have said. If I insert new column to the table and from the tool select it for auditing then it prompts that the audit table will be dropped and recreated and if I rename any column then it prompts that do you want to save the previous audit log(Yes/No). Is it correct?

Thanks

Hi,

I'm unable to reproduce it. Are you adding new column to the end of the table or somewhere in a middle?

Can you provide DDL for your table so we can see what else might be causing that behavior?

Thanks

Hi,

I am adding new column to the end of the table.

The DDL is,

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Customer](
[id] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](25) NULL,
[lastName] [varchar](25) NULL,
[middleName] [varchar](1) NULL,
[CustomerId] [varchar](25) NULL,
[address1] [varchar](250) NULL,
[address2] [varchar](25) NULL,
[city] [varchar](25) NULL,
[state] [varchar](25) NULL,
[zipCode] [varchar](25) NULL,
[eMail] [varchar](25) NULL,
[dob] [datetime] NULL,
[gender] [char](1) NULL,
[height] [varchar](25) NULL,
[weight] [varchar](25) NULL,
[country] [varchar](25) NULL,
[primaryPhone] [varchar](25) NULL,
[secondaryPhone] [varchar](25) NULL,
[type] [varchar](50) NULL,
[pgid] [int] NULL,
[auditCheck] [int] NULL,
[audit] [varchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Thanks

Thank you. Give me some time to test it in my test audit database. From the DDL I assume this is a SQL Server 2005 or 2008 database.