SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Auditing Alter Table

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Auditing Alter Table
Author Message
raviraj



Joined: 31 Aug 2011
Posts: 16
Country: India

Post Auditing Alter Table Reply with quote
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?
Wed Aug 31, 2011 12:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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
Wed Aug 31, 2011 1:17 pm View user's profile Send private message
raviraj



Joined: 31 Aug 2011
Posts: 16
Country: India

Post Reply with quote
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,
Tue Sep 06, 2011 12:36 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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
Wed Sep 07, 2011 9:32 am View user's profile Send private message
raviraj



Joined: 31 Aug 2011
Posts: 16
Country: India

Post Reply with quote
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
Thu Sep 08, 2011 3:16 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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
Fri Sep 09, 2011 12:23 pm View user's profile Send private message
raviraj



Joined: 31 Aug 2011
Posts: 16
Country: India

Post Reply with quote
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
Tue Sep 13, 2011 8:30 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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.
Tue Sep 13, 2011 9:42 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.